Scribd
Upload
28 views

My SQLData

The document discusses various SQL commands for creating, populating, and querying a database table. It shows how to: 1) Create a table with different data types and insert records; 2) Perform queries using operators, functions and filters on the table columns; 3) Add, modify and reorder columns in the table structure.

Uploaded by

Raheman RF
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
28 views

My SQLData

The document discusses various SQL commands for creating, populating, and querying a database table. It shows how to: 1) Create a table with different data types and insert records; 2) Perform queries using operators, functions and filters on the table columns; 3) Add, modify and reorder columns in the table structure.

Uploaded by

Raheman RF
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 15

* Create Table with Table name with Table field

mysql> CREATE TABLE aaa (

-> id INT,

-> name VARCHAR(255),

-> position VARCHAR(255),

-> company VARCHAR(255),

-> salary DOUBLE,

-> date DATE

-> );

* Insert Field Into Table

insert into aaa(id,name,work,company,salary)

-> values('1','Raheman','JavaDev','MNC',7.0);

--------------------------------------------------------------------------------------

* Select * from rahemanhyd.aaa;

+------+---------+---------+---------+--------+

| id | name | work | company | salary |

+------+---------+---------+---------+--------+

| 1 | Raheman | JavaDev | MNC | 7.0 |

+------+---------+---------+---------+--------+

-----------------------------------------------------------

* Insert 15 Record in Table

mysql> INSERT INTO aaa (id, name, work, company, salary)


VALUES

-> (1, 'John Doe', 'Developer', 'ABC Inc', '75000.50'),

-> (2, 'Jane Smith', 'Designer', 'XYZ Corp', '60000.75'),

-> (3, 'Bob Johnson', 'Manager', '123 Co', '90000.25'),

-> (4, 'Alice Brown', 'Engineer', '456 Ltd', '80000.00');

------------------------------------------------------------------------------

* Using Like [Search Data];

mysql> SELECT * FROM rahemanhyd.aaa WHERE

name like '%Bob%' Or

work like '%Pro%' OR

company like '%Data%' Or

salary Like '%37%';

+------+--------------+-----------------+------------------------+----------+

| id | name | work | company | salary |

+------+--------------+-----------------+------------------------+----------+

| 3 | Bob Johnson | Manager | 123 Co | 90000.25 |

| 7 | David Miller | Programmer | Tech Solutions | 72000.80 |

| 14 | Kelly Davis | Data Analyst | Data Insights | 67000.40 |

| 15 | Leo Brown | Project Manager | Project Management Inc | 95000.15 |

| 18 | Bob Johnson | Manager | 123 Co | 90000.25 |

+------+--------------+-----------------+------------------------+----------+

----------------------------------------------------------------------------------------------

mysql> select name, company, salary from rahemanhyd.aaa where salary>=30000.0 AND work
='Manager';

+-------------+---------+----------+

| name | company | salary |


+-------------+---------+----------+

| Bob Johnson | 123 Co | 90000.25 |

| Bob Johnson | 123 Co | 90000.25 |

+-------------+---------+----------+

-----------------------------------------------------

select * from rahemanhyd.aaa where work = 'Manager' AND (company = 'SalesPro' OR name = 'Bob
Johnson');

+------+-------------+---------+---------+----------+

| id | name | work | company | salary |

+------+-------------+---------+---------+----------+

| 3 | Bob Johnson | Manager | 123 Co | 90000.25 |

| 18 | Bob Johnson | Manager | 123 Co | 90000.25 |

+------+-------------+---------+---------+----------+

-----------------------------------------------------------------

mysql> select * from rahemanhyd.aaa where name = 'Bob Johnson' OR (work = 'Manager' AND
Salary>30000);

+------+-------------+---------+---------+----------+

| id | name | work | company | salary |

+------+-------------+---------+---------+----------+

| 3 | Bob Johnson | Manager | 123 Co | 90000.25 |

| 18 | Bob Johnson | Manager | 123 Co | 90000.25 |

+------+-------------+---------+---------+----------+

----------------------------------------------------------------------------

mysql> select * from rahemanhyd.aaa where name = 'Bob Johnson' OR (work = 'Managers' AND
Salary>30000);

+------+-------------+---------+---------+----------+
| id | name | work | company | salary |

+------+-------------+---------+---------+----------+

| 3 | Bob Johnson | Manager | 123 Co | 90000.25 |

| 18 | Bob Johnson | Manager | 123 Co | 90000.25 |

+------+-------------+---------+---------+----------+

----------------------------------------------------------------------------

select * from rahemanhyd.aaa where salary NOT IN (67000.0, 90000.0);

+------+----------------+----------------------+------------------------+----------+

| id | name | work | company | salary |

+------+----------------+----------------------+------------------------+----------+

| 1 | Raheman | JavaDev | MNC | 7.0 |

| 1 | John Doe | Developer | ABC Inc | 75000.50 |

| 2 | Jane Smith | Designer | XYZ Corp | 60000.75 |

| 3 | Bob Johnson | Manager | 123 Co | 90000.25 |

+------+-------------+---------+---------+----------+------+-------------+---------+

---------------------------------------------------------------------------------------------------

mysql> select * from rahemanhyd.aaa where salary BETWEEN 70000.0 AND 90000.0;

+------+----------------+------------+-----------------+----------+

| id | name | work | company | salary |

+------+----------------+------------+-----------------+----------+

| 1 | John Doe | Developer | ABC Inc | 75000.50 |

| 4 | Alice Brown | Engineer | 456 Ltd | 80000.00 |

| 5 | Charlie Wilson | Analyst | 789 Enterprises | 70000.50 |

---------------------------------------------------------------------------------
mysql> select * from rahemanhyd.aaa ORDER BY salary ASC;

+------+----------------+----------------------+------------------------+----------+

| id | name | work | company | salary |

+------+----------------+----------------------+------------------------+----------+

| 10 | Grace Lee | Sales Representative | SalesPro | 50000.60 |

| 6 | Eva Davis | Marketing | CCC Group | 55000.25 |

| 17 | Eva Davis | Marketing | CCC Group | 55000.25 |

| 12 | Ivy Green | Customer Support | Support Solutions | 58000.90 |

------------------------------------------------------------------------------------------------

mysql> select * from rahemanhyd.aaa order by salary DESC;

+------+----------------+----------------------+------------------------+----------+

| id | name | work | company | salary |

+------+----------------+----------------------+------------------------+----------+

| 15 | Leo Brown | Project Manager | Project Management Inc | 95000.15 |

| 11 | Henry Carter | Researcher | SciTech Labs | 92000.30 |

| 3 | Bob Johnson | Manager | 123 Co | 90000.25 |

--------------------------------------------------------------------------------------------------

select * from rahemanhyd.aaa where (salary BETWEEN 70000.0 AND 90000.0) AND company IN
('MNC', 'SalesPro');

-----------------------------------------------------------------------------------------------------------------

Alias Name Change =

mysql> select name AS std_name from rahemanhyd.aaa;

+----------------+
| std_name |

+----------------+

| Raheman |

| John Doe |

| Jane Smith |

| Bob Johnson |

| Alice Brown |

| Charlie Wilson |

--------------------------------------------------------------------------------------

* Arithmetic Operator In SQL

mysql> select id,name,mark, mark+100 from rahemanhyd.ddd;

+------+----------+------+----------+

| id | name | mark | mark+100 |

+------+----------+------+----------+

| 1 | Shah | 150 | 250 |

| 2 | Kapoor | 100 | 200 |

| 3 | Ghongade | 200 | 300 |

| 4 | Patil | 400 | 500 |

| 1 | Aarav | 76 | 176 |

---------------------------------------------------------------------------

mysql> select id,name,mark, mark+100 AS newmark from rahemanhyd.ddd;

+------+----------+------+---------+

| id | name | mark | newmark |

+------+----------+------+---------+

| 1 | Shah | 150 | 250 |

| 2 | Kapoor | 100 | 200 |

| 3 | Ghongade | 200 | 300 |


| 4 | Patil | 400 | 500 |

-----------------------------------------------------------------------------

mysql> select id,name,mark, mark+id AS idmark from rahemanhyd.ddd;

+------+----------+------+--------+

| id | name | mark | idmark |

+------+----------+------+--------+

| 1 | Shah | 150 | 151 |

| 2 | Kapoor | 100 | 102 |

| 3 | Ghongade | 200 | 203 |

| 4 | Patil | 400 | 404 |

-------------------------------------------------------------------------------

* Create New Coulnm In Table

ALTER TABLE rahemanhyd.ddd ADD COLUMN newint INT;

mysql> select * from rahemanhyd.ddd;

+------+----------+------+--------+

| id | name | mark | newint |

+------+----------+------+--------+

| 1 | Shah | 150 | NULL |

| 2 | Kapoor | 100 | NULL |

| 3 | Ghongade | 200 | NULL |

| 4 | Patil | 400 | NULL |

---------------------------------------------------------------------
* create multiple column use AFTER TABLE

* mysql> ALTER TABLE rahemanhyd.ddd

-> ADD COLUMN location varchar(255),

-> ADD COLUMN salary DOUBLE,

-> ADD COLUMN price INT;

** mysql> ALTER TABLE rahemanhyd.ddd

-> ADD COLUMN location varchar(255),

-> ADD COLUMN salary DOUBLE,

-> ADD COLUMN price INT FIRST; //it will take first field to add column

** mysql> ALTER TABLE rahemanhyd.ddd

-> ADD COLUMN location varchar(255),

-> ADD COLUMN salary DOUBLE,

-> ADD COLUMN price INT LAST; //it will take Last field to add column

** mysql> ALTER TABLE rahemanhyd.ddd

-> ADD COLUMN lastdata INT AFTER mark;

mysql> select * from rahemanhyd.ddd;

+------+----------+------+----------+--------+----------+--------+-------+

| id | name | mark | lastdata | newint | location | salary | price |

+------+----------+------+----------+--------+----------+--------+-------+

| 1 | Shah | 150 | NULL | NULL | NULL | NULL | NULL |

| 2 | Kapoor | 100 | NULL | NULL | NULL | NULL | NULL |

| 3 | Ghongade | 200 | NULL | NULL | NULL | NULL | NULL |


----------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

**Unique Key = > Can not be duplicate it unique akmev

mysql> ALTER TABLE rahemanhyd.ddd

-> ADD COLUMN idbaad INT UNIQUE KEY AFTER id;

mysql> select * from rahemanhyd.ddd;

+------+--------+----------+------+----------+--------+----------+--------+-------+

| id | idbaad | name | mark | lastdata | newint | location | salary | price |

+------+--------+----------+------+----------+--------+----------+--------+-------+

| 1 | NULL | Shah | 150 | NULL | NULL | NULL | NULL | NULL |

| 2 | NULL | Kapoor | 100 | NULL | NULL | NULL | NULL | NULL |

| 3 | NULL | Ghongade | 200 | NULL | NULL | NULL | NULL | NULL |

------------------------------------------------------------------------------------------------------

**Change Column Name

** mysql> ALTER TABLE rahemanhyd.ddd

-> CHANGE COLUMN newint newintdata varchar(255);

mysql> select * from rahemanhyd.ddd;

+------+--------+----------+------+----------+------------+----------+--------+-------+

| id | idbaad | name | mark | lastdata | newintdata | location | salary | price |

+------+--------+----------+------+----------+------------+----------+--------+-------+

| 1 | NULL | Shah | 150 | NULL | NULL | NULL | NULL | NULL |

| 2 | NULL | Kapoor | 100 | NULL | NULL | NULL | NULL | NULL |

| 3 | NULL | Ghongade | 200 | NULL | NULL | NULL | NULL | NULL |


* Change Multiple column Name - >

** mysql> ALTER TABLE rahemanhyd.ddd

-> CHANGE COLUMN newint newintdata varchar(255),

-> CHANGE COLUMN location adress varchar(255);

----------------------------------------------------------------------------------------------

** DROP TABLE -->

mysql> ALTER TABLE rahemanhyd.ddd

-> DROP COLUMN idbaad;

mysql> select * from rahemanhyd.ddd;

+------+----------+------+----------+------------+----------+--------+-------+

| id | name | mark | lastdata | newintdata | location | salary | price |

+------+----------+------+----------+------------+----------+--------+-------+

| 1 | Shah | 150 | NULL | NULL | NULL | NULL | NULL |

| 2 | Kapoor | 100 | NULL | NULL | NULL | NULL | NULL |

----------------------------------------------------------------------------------------------

** Drop Table

DROP TABLE rahemanhyd.ccc;

** ALTER TABLE rahemanhyd.ddd

-> DROP COLUMN mark;

--------------------------------------
** UPDATE data into table

** mysql> UPDATE rahemanhyd.ddd

-> SET lastdata = 1000,location = 'Hyderabad'

-> WHERE id = 1;

** if u use this Then it will add lastdata 1000 and location hyderaad for all record

every recpord location will be hyderabad ->

** mysql> UPDATE rahemanhyd.ddd

-> SET lastdata = 1000,location = 'Hyderabad'; // use this for update all record

mysql> select * from rahemanhyd.ddd;

+------+----------+------+----------+------------+-----------+--------+-------+

| id | name | mark | lastdata | newintdata | location | salary | price |

+------+----------+------+----------+------------+-----------+--------+-------+

| 1 | Shah | 150 | 1000 | NULL | Hyderabad | NULL | NULL |

| 2 | Kapoor | 100 | NULL | NULL | NULL | NULL | NULL |

-----------------------------------------------------------------------------------------

** UPADATE WITH SET

mysql> UPDATE rahemanhyd.eee

-> SET result =

-> CASE

-> WHEN mark >=300 THEN 'First'

-> WHEN mark <=300 AND mark>=250 THEN 'Second'


-> ELSE 'FAIL'

-> END;

mysql> select * from rahemanhyd.eee;

+----+-------+----------+------+--------+

| id | name | location | mark | result |

+----+-------+----------+------+--------+

| 1 | John | CityA | 200 | FAIL |

| 2 | Alice | CityB | 260 | Second |

| 3 | Bob | CityC | 310 | First |

+----+-------+----------+------+--------+

-----------------------------------------------------------------

** Delete Data MySQL ->

DELETE from rahemanhyd.ddd where id IS NULL; // It will delete all record

-------------------------------------------------------------------------------------

//Copy Table into ANother Table

** Take table no 1 data and copy in table no 2

Step 1 = mysql> CREATE TABLE rahemanhyd.ded LIKE rahemanhyd.ddd;

Step 2 = mysql> INSERT rahemanhyd.ded select * from rahemanhyd.ddd;

Step 3 = mysql> select * from rahemanhyd.ded;

// Then All Record will show

--------------------------------------------------------------------------------
** MIN / MAX -->

** mysql> select MIN(mark) as marks from rahemanhyd.ddd;

+-------+

| marks |

+-------+

| 76 |

+-------+

--------------------------------------------------------------

** Total Marks

mysql> select SUM(mark) AS totalMark from rahemanhyd.ddd;

+-----------+

| totalMark |

+-----------+

| 1925 |

+-----------+

** mysql> select AVG(mark) AS totalMark from rahemanhyd.ddd;

** mysql> select COUNT(mark) from rahemanhyd.ddd;

** mysql> select UPPER(name) from rahemanhyd.ddd; // ur all name will represent UPPERCASE

** mysql> select LOWER(name) from rahemanhyd.ddd; // ur all name will represent LOWER

---------------------------------------------------------------------------------------------------

//Concat

** mysql> select id, CONCAT(name,mark) from rahemanhyd.ddd;

+------+-------------------+
| id | CONCAT(name,mark) |

+------+-------------------+

| 1 | Shah150 |

| 2 | Kapoor100 |

| 3 | Ghongade200 |

| 4 | Patil400 |

//USING SPACE

** mysql> select id, CONCAT(name,' ',mark) from rahemanhyd.ddd;

+------+-----------------------+

| id | CONCAT(name,' ',mark) |

+------+-----------------------+

| 1 | Shah 150 |

| 2 | Kapoor 100 |

| 3 | Ghongade 200 |

| 4 | Patil 400 |

-------------------------------------------------------------------------------------------

//REVERSE()

** mysql> select REVERSE(name) from rahemanhyd.ddd;

+---------------+

| REVERSE(name) |

+---------------+

| hahS |

| roopaK |

| edagnohG |

| litaP |

| varaA |
| ahsiA |

------------------------------------------------------------------------------------------

You might also like