My SQL DML Commands By Prof B A

My. SQL DML Commands By Prof. B. A. Khivsara Note: The material to prepare this presentation has been taken from internet and are generated only for students reference and not for commercial use.

Design at least 10 SQL queries for suitable database application using SQL DML statements • Insert, • Select, • Update, • Delete • with operators, • functions and • set operator.

Insert - INSERT tuples into a relation Where every attribute in the table is given a value INSERT INTO tablename VALUES (attr 1_value, attr 2_value, . . . attrn_value) Where every attribute in the table is has not a value then it considered as NULL. INSERT INTO tablename (attrx, . . . , attry) VALUES (attrx_value, . . . , attry_value) Example: • Insert into Stud values (1, ’Pooja’, ‘Pune’) • Insert into Stud (rollno, name) values (2, ‘Amit’) The output of "Stud" table after inserting above 2 rows rollno Name Address 1 Pooja Pune 2 Amit

Delete –DELETE tuples from relation • Syntax • DELETE FROM tablename <WHERE condition> • It removes zero, one or many tuples; The WHERE clause is optional; if it is left out all tuples are removed, but the table still exists; it is empty. • Example: 1> DELETE FROM Stud WHERE Address = 'Pune'; ( Only delete roes that satisfy the condition) 2> DELETE FROM Stud; • (Delete all rows)

Update- UPDATE of one or more selected tuples Syntax UPDATE tablename SET attrx = new_value, <WHERE condition>; Example: 1> Update Stud Set Name= ‘Shruti’ where rollno=1 (Only name field of row having rollno as 1 will be chaged as ‘Shruti’) 2> Update Stud Set Name= ‘Shruti’ (name field of All row will be changed as ‘Shruti’)

Select- SELECT statement retrieves data from table Syntax SELECT attribute_list FROM table_list <WHERE condition>; • The attributes are those you want to see in the result, • the tables are those required for the query, • the condition is a boolean expression that specifies which tuples are to be retrieved by the query. Example: 1. 2. 3. 4. 5. Select * from Stud; Select * from Stud where rno=1; Select Name, Address from Stud where rno=1 Select Name from Stud where address=‘Nashik’;

Order By- Retrieval with ordering Ascending/ Descending • The ORDER BY keyword is used to sort the result-set by a specified column. • The ORDER BY keyword sort the records in ascending order by default. • If you want to sort the records in a descending order, you can use the DESC keyword. Syntax SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC Example 1. Select * from Stud order by rno desc; 2. Select * from Stud order by rno ; 3. Select * from Stud order by Name;

LIKE Operator- search for a specified pattern in a column Syntax SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern SQL Wildcards • SQL wildcards can substitute for one or more characters when searching for data in a database. • SQL wildcards must be used with the LIKE operator. Wildcard Description % A substitute for zero or more characters _ A substitute for exactly one character

LIKE Operator- Example "Persons" table P_Id Last. Name Address 1 Heena Pune 2 Savita Pune 3 Sarika Bombay We use the following SELECT statement: SELECT Last. Name from Person where Last. Name like ‘S%’ ; The result-set will look like this: Last. Name Savita Sarika

DISTINCT Operator • The DISTINCT keyword can be used to return only distinct (different) values. Syntax • SELECT DISTINCT column_name(s) FROM table_name Example The "Persons" table: P_Id Name Address 1 Hansen Pune 2 Svendson Pune 3 Pettersen Bombay • SELECT distinct(Address) from Person Address • The result-set will look like this Pune Bombay

IN Operator- The IN operator allows you to specify multiple values in a WHERE clause. Syntax SELECT column_name(s) FROM table_name WHERE column_name IN (value 1, value 2, . . . ) Example The "Persons" table: P_Id Name Address 1 Hansen Pune 2 Svendson Nashik 3 Pettersen Bombay SELECT * FROM Persons WHERE Address IN ('Pune', 'Bombay') The result-set will look like this P_Id Name Address 1 Hansen Pune 3 Pettersen Bombay

NULL Operator • To test if a value is NULL you cannot use =, because every NULL is considered distinct from every other one. Must use "IS NULL" or "IS NOT NULL“ Syntax SELECT Attr_List FROM table_name WHERE Attr_name IS NULL; Example 1. SELECT * FROM Stud WHERE Address is null; 2. SELECT * FROM Stud WHERE Address is not null;

Aggregate Functions • Aggregate /Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. • The Functions are as below: MIN returns the smallest value in a given column MAX returns the largest value in a given column SUM returns the sum of the numeric values in a given column AVG returns the average value of a given column COUNT returns the total number of values in a given column COUNT(*) returns the number of rows in a table

Count()- Syntax COUNT(column_name)-The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column: Syntax • SELECT COUNT(column_name) FROM table_name COUNT(*)-The COUNT(*) function returns the number of records in a table: Syntax • SELECT COUNT(*) FROM table_name

Count()- Example • Consider an employee_tbl table as shown below SELECT COUNT(*) FROM employee_tbl ; Output Count(*) 7 SELECT COUNT(*) FROM employee_tbl WHERE name="Zara"; Count(*) 2 Output

Avg(), Min(), Max(), Sum() • • Syntax SELECT AVG(column_name) FROM table_name Example SELECT AVG(Marks) FROM Stud • • Syntax SELECT Min(column_name) FROM table_name Example SELECT Min(Marks) FROM Stud • Syntax of all other functions are similar.

GROUP BY & Having Clause • The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement • Having is similar to where, to give condition, but it can only work with group by Syntax SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ;

GROUP BY & Having Clause- Example Products Table id name type price 123451 Park's Great Hits Music 19 123452 Silly Puddy Toy 5 123453 Playstation Toy 89 123454 Men's T-Shirt Clothing 32 123455 Blouse Clothing 34 123456 Electronica 2002 Music 3 SELECT type, MIN(price) FROM products GROUP BY type Type Min(Prize) Music 3 Toy 5 Clothing 32 Out Put SELECT type, Min(price) FROM products GROUP BY type having Min(price)>4 Type Min(Prize) Toy 5 Clothing 32

Set Operator: UNION, UNION ALL UNION operator displays the combined result from all the compounded SELECT queries, after removing all duplicates and in sorted order (ascending by default), without ignoring the NULL values. UNION ALL gives the result set without removing duplication and sorting the data.

Set Operator- Example Union and Union all Emp Table ENo Ename Addr 101 Jenny Mumbai 102 Akash Pune 103 Sona Pune 104 Minal Nashik 105 Raksh Jalgoan Proj Table Pr. No Addr 10 Mumbai 20 Pune 30 Pune Addr Jalgoan Addr SELECT City FROM Emp UNION SELECT City FROM Proj ORDER BY City; O/P Jalgoan Mumbai Nashik Mumbai SELECT City FROM Emp UNION All SELECT City FROM Proj ORDER BY City; Pune Mumbai Nashik Pune O/P Pune

Assignment Create Employee table, Project table and add rows shown below Eid EName Address Salary Commision Pr. No Addr 1 2 3 4 Amit Sneha Savita Pooja Sagar Pune Nasik Mumbai 35000 28000 19000 25000 2000 3000 10 Mumbai 20 Pune 30 Jalgoan 5 1. Find different locations from where employees belong to? 2. What is maximum and minimum salary? 3. Display the content of employee table according to the ascending order of salary amount. 4. Find the name of employee who lived in Nasik or Pune city. 5. Find the name of employees who does not get commission. 6. Change the city of Amit to Nashik. 7. Find the information of employees whose name starts with ‘A’. 8. Find the count of staff from Mumbai. 9. Find the count of staff from each city 10. Find the address from where employees are belonging as well as where projects are going on. 11. Find city wise minimum salary. 12. Find city wise maximum salary having maximum salary greater than 26000 13. Delete the employee who is having salary greater than 30, 000.

References • • https: //www. tutorialspoint. com/mysql/ https: //dev. mysql. com/doc/refman/5. 5/ https: //www. w 3 schools. com/sql/ http: //www. mysqltutorial. org/