UNCA CSCI 343 - SQL notes (27 pages)

Previewing pages 1, 2, 3, 25, 26, 27 of 27 page document View the full content.
View Full Document

SQL notes



Previewing pages 1, 2, 3, 25, 26, 27 of actual document.

View the full content.
View Full Document
View Full Document

SQL notes

44 views

Other


Pages:
27
School:
University of North Carolina, Asheville
Course:
Csci 343 - Database Management Systems
Database Management Systems Documents

Unformatted text preview:

SQL Part 2 Much of the material presented in these slides was developed by Dr Ramon Lawrence at the University of Iowa SELECT Statement Overview SELECT list of column expressions FROM list of tables and join operations WHERE list of logical expressions for rows GROUP BY list of grouping columns HAVING list of logical expressions for groups ORDER BY list of sorting specifications Expression combination of columns constants operators and functions Example Relations Relations Emp eno ename bdate title salary supereno dno Proj pno pname budget dno Dept dno dname mgreno WorksOn eno pno resp hours Foreign keys Emp Emp supereno to Emp eno Emp dno to Dept dno Proj Proj dno to Dept dno Dept Dept mgreno to Emp eno WorksOn WorksOn eno to Emp eno WorksOn pno to Proj pno Example Relation Instances But First Join Revisited Cross Product Style How many columns in the output table Natural Join Style How many columns in the output table Alternative SQL92 styles How many columns in each output table SELECT FROM emp dept WHERE emp dno dept dno SELECT FROM emp NATURAL JOIN dept SELECT FROM emp INNER JOIN dept USING dno SELECT FROM emp INNER JOIN dept ON emp dno dept dno More Join Practice Relational database schema emp eno ename bdate title salary supereno dno proj pno pname budget dno dept dno dname mgreno workson eno pno resp hours Return a list of all department names the names of the projects of that department and the name of the manager of each department Return the names of all projects and the names of the employees who have worked on each project Return the names of all employees who are supervisors Ordering Result Data The query result returned is not ordered on any attribute by default We can order the data using the ORDER BY clause SELECT ename salary bdate FROM emp WHERE salary 30000 ORDER BY salary DESC ename ASC ASC sorts the data in ascending order and DESC sorts it in descending order The default is ASC The order of sorted attributes is significant The first attribute



View Full Document

Access the best Study Guides, Lecture Notes and Practice Exams

Loading Unlocking...
Login

Join to view SQL notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view SQL notes and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?