1Structured Query Language allows to store , manipulate, control and retrieve the information from Relational Database Management Systems. Following are some RDBMS products. Oracle Databse, Microsoft SQL Server, MySQL, PostgreSQL etc.
SQL commands can be categorized under
- DDL - Data Definition Language (CREATE, DROP, ALTER)
- DML- Data Manipulation Language (INSERT, DELETE, UPDATE)
- DCL- Data Control Language (GRANT, REVOKE)
- DQL- Data Query Language (SELECT)
In this session we will focus on DQL SELECT Statement
SELECT T2. DepartmentName, T1. StudentName, SUM(MARKS)
FROM Students T1, Departments T2, MARKS T3
WHERE T1.DEPTCODE=T2.DEPTCODE
AND T1.STUDENTID=T3.STUDENTID
AND T3.EXAMNAME='SEMESTER 1'
GROUP BY T2. DepartmentName,T1. StudentName
HAVING SUM(MARKS)> 900
ORDER BY T1.StudentName
This is old fashioned way of writing clean SQL statements, New way of writing statements to achieve the same result will be discussed in the actual course.
SELECT Clause: Here one has to list the columns in a table(s) to display as a result of the query. columns can be from the list of tables mentioned in FROM clause. A from clause can have a sub query instead of a physical table or view. we will cover this in detail, don't get lost. We can also use Oracle functions or custom functions to return single value return functions.
FROM Clause: This is to include the tables and optionally subqueries (logical table with in memory)
WHERE Clauase: This is to filter the results of the entire select statement. you can exclude or include certain criteria. Apart from this you also join other tables with appropriate unique column to match appropriate record/row with appropriate record/ row in other table.
GROUP BY Clause: This is to group set of columns to use aggregate functions. example Average sales by city or state etc.
HAVING Clause: This is to filter the group results, unlike where cluase it is applied at the group result. example Average Sales > 10000.
ORDER BY Clause: This is to sort the results using specific selected list of the columns in SELECT clause.
