1 / 27

More on SQL

Indra Budi indra@cs.ui.ac.id. More on SQL. True/False. SQL stands for Standard Query Language . SQL is only a data manipulation language (DML). The SQL keyword CREATE is used to name a new table and describe the table's columns.

zariel
Download Presentation

More on SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Indra Budi indra@cs.ui.ac.id More on SQL

  2. True/False • SQL stands for Standard Query Language. • SQL is only a data manipulation language (DML). • The SQL keyword CREATE is used to name a new table and describe the table's columns. • An asterisk (*) following the SELECT verb means that all columns are to be obtained. • The WHERE clause contains the condition that specifies which columns are to be selected.

  3. Comparison Operators Equals = Not equals <> Greater than > Less than < Greater than or equal to >= Less than or equal to <= Within a list of values IN A logical NOT Within a range BETWEEN

  4. The DISTINCT Qualifier • Eliminating duplicate rows on the output… SELECT DISTINCT StateAddress FROM Employee;

  5. IN a List of Values SELECT StudentName FROM Student WHERE State IN [‘PA’, ‘MA’, ‘CA’];

  6. The Logical NOT SELECT StudentName FROM Students WHERE State NOT IN [‘NJ’, ‘NM’, ‘NY’]; SELECT StudentName FROM Students WHERE NOT GradePointAverage >= 3.0;

  7. Within a Range of Values SELECT StudentName FROM Student WHERE StudentID BETWEEN 250 and 300;

  8. Using Wildcard Character Substitutions • The LIKE keyword is used in place of the = sign when you use wildcard characters. • The underscore character (_) is a single character substitution • The percent character (%) is a multi-character substitution

  9. Using LIKE SELECT StudentID FROM Student WHERE StudentName LIKE ‘K%’; SELECT PartName FROM Part WHERE PartNumber LIKE ‘_ABC%’;

  10. NULL Means Nothing • A NULL character means that nothing has been entered. This is different from a space or a zero. SELECT Name FROM Student WHERE Major IS NULL;

  11. ORDER BY… Sorting Outputs • Sorting in descending order… SELECT StudentID, Name FROM Student ORDER BY Name DESC; • Sorting in ascending order… SELECT StudentID, Name FROM Student ORDER BY Name ASC;

  12. Built-in Functions • Counting number of rows COUNT • Adding the values in a column SUM • Averaging the values in a column AVG • Finding the maximum value in a column MAX • Finding the minimum value in a column MIN

  13. Built-in Functions SELECT Count (*) FROM Student WHERE State = ‘WI’; SELECT Sum (Amount) FROM SalesReceipt; SELECT Max (Score) FROM Assignments;

  14. Grouping the Output SELECT Name, State FROM Student GROUP BY State;

  15. Reducing the Groups Displayed SELECT Name, State FROM Student GROUP BY State HAVING Count (*) > 4;

  16. Sub-Queries SELECT Name FROM Student WHERE SID IN (SELECT StudentNumber FROM Enrollment WHERE ClassName = ‘MIS445’);

  17. Joining Tables SELECT Student.SID, Student.Name, Enrollment.ClassName FROM Student, Enrollment WHERE Student.SID = Enrollment.StudentNumber AND Student.State = ‘OH’;

  18. EXISTS SELECT DISTINCT StudentNumber FROM Enrollment A WHERE EXISTS (SELECT * FROM Enrollment B WHERE A.StudentNumber = B.StudentNumber AND A.ClassName NOT = B.ClassName);

  19. Input, Delete & Modify Inputting Data INSERT INTO Enrollment VALUES (400, ‘MIS445’, 44); Deleting Data DELETE Student WHERE Student.SID = 100; Modify Data UPDATE Enrollment SET SeatNumber = 44 WHERE SID = 400;

  20. Suppliers(sid, sname, address) • Parts(pid, pname, color, lifetime) • Catalog(sid, pid, price)

  21. Write the Relational Algebra & SQL • Find the names of suppliers who supply some red part • Find the sids of suppliers who supply some red or green part.

  22. Exercise • Flights(fno, from, to,distance,departs, arrives) • Aircraft(aid, aname, cruisingrange) • Certified(eid, aid) • Employees(eid, ename, salary)

  23. Find the eids of pilots certified for some Boeing aircraft. • Find the eids of employees who make the highest salary.

  24. Is this Relation in 1NF, 2 NF & 3NF ? • Book(Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher) • FDs • Book_title  Publisher, Book_type • Book_type --> Listprice • Authorname  Author_affil

  25. The key for this relation is Book_title,Authorname. This relation is in 1NF and not in 2NF as no attributes are FFD (Fully Functionally Dependent) on the key. It is also not in 3NF. • 2NF decomposition: • Book0(Book_title, Authorname) • Book1(Book_title, Publisher, Book_type, Listprice) • Book2(Authorname, Author_affil) • This decomposition eliminates the partial dependencies. • 3NF decomposition: • Book0(Book_title, Authorname) • Book1-1(Book_title, Publisher, Book_type) • Book1-2(Book_type, Listprice) • Book2(Authorname, Author_affil) • This decomposition eliminates the transitive dependency of Listprice

  26. Given a relation Rwith four attributes, XYZW, and a set of functional dependencies F • F:  XY  --> Z, XY --> W, Z --> X, W --> Y • Identify the candidate key(s) for R. • Identify the best normal form that Rsatisfies (1NF, 2NF, 3NF, or BCNF). • If R is not in BCNF decompose it into a set of BCNF relations that preserve the dependencies.

  27. Candidate keys: XY, ZW, ZY, XW • R is in 3NF but not BCNF due to the FDs: Z --> X and W --> Y. Neither W nor Z is a candidate key. • One possible decomposition that is in BCNF is XZ, WZ and WY. But this decomposition does not preserve the dependencies XY --> Z and XY --> W. If we include a relation such as XYZ then we are left with the violating FD:  Z --> X. Similarly for XYW. So, there is no dependency-preserving decomposition into BCNF unless we do a join.

More Related