Database_Systems_Lab_5_Presentation.pptx

Database system course lab

1. Lab 5: Advanced SQL Statements and Functions Objective: Dive deeper into SQL with advanced statements and functions to manipulate and retrieve data effectively.

2. SQL UPDATE Statement The UPDATE statement modifies existing records in a table. Always ensure to use the WHERE clause to specify which record(s) to update.

3. Example: SQL UPDATE UPDATE Courses SET Credits = 4 WHERE CourseName = 'Algorithms';

4. Exercise: SQL UPDATE Modify the 'CourseName' of the course with ID 101 to 'Advanced Databases'.

5. SQL DELETE Statement The DELETE statement removes one or more records from a table. Be cautious, and always use the WHERE clause to avoid deleting all records.

6. Example: SQL DELETE DELETE FROM Students WHERE Age < 18;

7. Exercise: SQL DELETE Remove all courses from the 'Courses' table that have less than 3 credits.

8. SQL SELECT TOP Statement The SELECT TOP clause is used to specify the number of top records to return from a table.

9. Example: SQL SELECT TOP SELECT TOP 3 * FROM Students ORDER BY Age DESC;

10. Exercise: SQL SELECT TOP Retrieve the top 5 courses from the 'Courses' table with the highest credits.

11. SQL ORDER BY Statement The ORDER BY statement is used to sort the result set based on one or more columns.

12. Example: SQL ORDER BY SELECT * FROM Students ORDER BY Name ASC;

13. Exercise: SQL ORDER BY Retrieve all records from the 'Courses' table ordered by 'CourseName' in descending order.

14. SQL Aliases Aliases are temporary names given to table or column for the purpose of a specific SQL query.

15. Example: SQL Aliases SELECT CourseID AS ID, CourseName AS Course FROM Courses;

16. Exercise: SQL Aliases Rename 'ID' column to 'StudentID' and 'Name' column to 'StudentName' in the 'Students' table for your query result.

17. SQL MIN() and MAX() Functions MIN() function returns the smallest value of the selected column. MAX() function returns the largest value of the selected column.

18. Example: MIN() and MAX() SELECT MIN(Age) AS Youngest, MAX(Age) AS Oldest FROM Students;

19. Exercise: MIN() and MAX() Find the course with the minimum credits and the course with the maximum credits in the 'Courses' table.

20. SQL COUNT(), AVG() and SUM() Functions COUNT() returns the number of rows that matches a specified criteria. AVG() returns the average value of a numeric column. SUM() returns the total sum of a numeric column.

21. Example: COUNT(), AVG(), SUM() SELECT COUNT(*) AS TotalStudents, AVG(Age) AS AverageAge, SUM(Fees) AS TotalFees FROM Students;

22. Exercise: COUNT(), AVG(), SUM() Count the total number of courses, average credits, and total credits in the 'Courses' table.

23. Advanced SQL Commands Understanding these advanced SQL commands and functions is crucial for efficient database management and data analysis.

24. Feedback and Questions Students are encouraged to provide feedback and ask questions at the end of the lab.