Lec8_Lab_CSC371_Database Systems course.pptx

Database system course lab

1. CSC371-Database Systems I Lecture-8 (Lab) (Spring2020) Abdul Qayyum [email protected] Samia Arshad [email protected] Faisal Mumtaz [email protected] 1

2. Previous Lecture Review Where Clause Operators used in SQL Logical Operators 2

3. 3 Staff Branch

4. Agenda SQL Update SQL Delete SQL Select Top SQL ORDER BY SQL Aliases SQL MIN() and MAX() Functions SQL COUNT(), AVG() and SUM() Functions 4

5. The SQL UPDATE Statement UPDATE  table_name SET  column1  =  value1 ,  column2  =  value2 , ... WHERE  condition ; Update staff set salary = 20000 where staffno ='SG14’ Update staff set salary = 20000 5

6. UPDATE Multiple Records Update staff set position='Supervisor’ where position='Assistant’ Update staff set salary=17000 where position='Supervisor' AND salary < 17000 6

7. The SQL DELETE Statement DELETE FROM  table_name   WHERE  condition ; DELETE FROM Staff WHERE  staffno = 'SA9’ DELETE FROM Staff WHERE  staffno = ‘SL21’ AND BranchNo =‘B005’ DELETE FROM Staff WHERE  staffno = ‘SA9’ Difference of Delete and Drop of table 7

8. SQL TOP, LIMIT SQL Server SELECT TOP  number | percent   column_name (s) FROM  table_name WHERE  condition ; SELECT TOP 3 * FROM Staff; SELECT TOP 50 PERCENT * FROM staff; SELECT TOP 3 * FROM Branch WHERE city=‘London'; MySQL SELECT  column_name (s) FROM  table_name WHERE  condition LIMIT  number ; SELECT * FROM Staff LIMIT 3; SELECT * FROM Branch WHERE City=‘London' LIMIT 3; 8

9. The SQL ORDER BY Keyword SELECT  column1 ,  column2, ... FROM  table_name ORDER BY  column1, column2, ...  ASC|DESC; SELECT * FROM Staff ORDER BY  fname ; SELECT * FROM Staff ORDER BY  lname  DESC; SELECT * FROM Staff ORDER BY Position, Salary; SELECT * FROM Staff ORDER BY Position ASC, Salary DESC; 9

10. SQL Aliases SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query. Aliases can be useful when: There are more than one table involved in a query Functions are used in the query Column names are big or not very readable Two or more columns are combined together. 10

11. Continue…….. SELECT  column_name  AS  alias_name FROM  table_name ; SELECT  fname As firstName , lname AS LastName from Staff; SELECT  fname As [First Name], lname AS [Last Name] from Staff; SELECT  StaffNo , fname + ', ‘ + lname  AS Name FROM Staff; MySQL/SQL Server SELECT  StaffNo , CONCAT( fname ,', ‘, lname ) AS  FullName FROM Staff; 11

12. The SQL MIN() and MAX() Functions SELECT MIN( column_name ) FROM  table_name WHERE  condition ; SELECT MAX( column_name ) FROM  table_name WHERE  condition ; SELECT MIN(salary) AS  MinmumSalary FROM Staff; SELECT Max(salary) AS  MaximumSalary FROM Staff; 12

13. The SQL COUNT(), AVG() and SUM() Functions SELECT COUNT( staffno ) AS [Total Staff] FROM Staff; SELECT  COUNT( branchno ) AS [Total Branches] FROM Branch; SELECT AVG(Salary) As [average Salary] FROM Staff ; SELECT AVG(Salary) As [average Salary] FROM Staff where branchno =‘B003’ ; NULL Values are ignored SELECT SUM(salary) As [Total Salary] FROM Staff; 13

14. Summary SQL Update SQL Delete SQL Select Top SQL ORDER BY SQL Aliases SQL MIN() and MAX() Functions SQL COUNT(), AVG() and SUM() Functions 14