1 / 40

Lecture 4 on Structural Query Language

Lecture 4 on Structural Query Language.

fiachra-philbin
Download Presentation

Lecture 4 on Structural Query Language

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. Lecture 4 on Structural Query Language To study Structural Query Language (SQL) as a non-procedural computer language to access relational database in data definitional language such as Create, Drop and Alter statements and in data manipulation language such as Insert, Update and Delete statements.

  2. Structural Query Language (SQL) • SQL is a transform oriented relational language. • SQL can be run as a query/update language by itself or SQL command can be embedded in application programs. • SQL include commands for data definition, data manipulation and data control. • It is non-procedural and includes no reference to access paths, links or navigation such that the user only need to specify what he wants to retrieve/update rather than how to do it. • It has a high level of independence from physical data storage.

  3. SQL syntax SELECT [Distinct] select-list FROM from-list WHERE qualification GROUP BY grouping-list HAVING group-qualification ORDER attribute-list

  4. Where The Where clause limits the rows that are returned from Query: For example: Select * from users where userid = 2

  5. Order by This command can sort by any column type: alphabetical, chronological or numeric in either ascending or descending order by placing asc or desc: For example: Select * from users order by lname, fname

  6. Having The having predicate restricts the rows displayed by a group by (whereas the where clause restricts the rows that are used in the group by). For example: Select avg(contribution) as avg_contrib, state from contributions group by state having avg(contribution) > 500

  7. Sample database for SQL Student Relation

  8. Enrollment Relation Class Relation

  9. SQL Projections The projection Student[Sid, Name, Major] is created by • Select Sid, Name, Major from Student • 100 Jones Hist 150 Parks Acct 200 Baker Math 250 Glass Hist 300 Baker Acct 350 Russell Math 400 Rye Acct 450 Jones Hist

  10. Select statement with Unique: Select Unique Major from Student => Hist Acct Math • Select statement with where clause: Select * from student where Major = ‘Math’ • 200 Baker Math Gr 50 350 Russell Math Jr 20 Note: The * in the SQL statement means that all attributes of the relation are to be obtained.

  11. Select statement with several conditions: Select Name, Age from Student where Major = ‘Math’ and Age > 21 • Baker 50 Select statement with an In clause: Select Name from Student where Major in [‘Math’, ‘Acct’] => Parks Baker Baker Russell Rye Select statement with an Not In clause: Select Name from Student where Major not in [‘Math’, ‘Acct’] => Jones Glass Jones

  12. Select statement with LIKE conditions Like operation allows a rich set of regular expressions to be used as patterns while searching text. For example, SELECT Age FROM student WHERE Name = ‘R_%E’ The only such student is Rye and his age is 18.

  13. SQL Built-in functions Some standard built-in functions are available in SQL as follows: • Count: computes the number of tuples in a relation • Sum: totals numeric attributes • Avg: computes the average value • Max: obtain the maximum value of an attribute • Min: obtain the minimum value of an attribute For example: Select Count(Major) from Student • 8 Select Count(Unique Major) from Student • 3 Select Sid, Name from Student where Age > Avg(Age) • 200 Baker 250 Glass 300 Baker

  14. Built-in functions and grouping Built-in functions can be applied to groups of tuples within a relation. Such groups are formed by collecting tuples together that have the same value of a specific attribute. The SQL keyword Group By instructs the DBMS to group tuples together that have the same value of an attribute. For example, the count function sums the number of tuples in each group/ Select Major, Count(*) from Student Group By Major => Hist 3 Acct 3 Math 2 For example, use SQL Having clause to identify the subset of groups we want to consider. Select Major, Avg(Age) from Student Group By Major Having Count(*)>2 • Hist 31.67 Acct 26

  15. Union, Intersect, Except • Union set operation is similar to “or” condition in where clause. • Intersect set operation is similar to “and” condition in where clause. • Except set operation is a difference set operation in relational algebra.

  16. Select Name from student where age < 20 or age > 40 Can be rewritten as Select Name from student where age < 20 Union Select Name from student where age > 40 The answer is Rye, Baker, and Glass.

  17. Select Name from student where age < 20 and Grade = SN Can be rewritten as Select Name from student where age < 20 INTERSECT Select Name from student where Grade = SN The answer is Jones

  18. Select Name from student where Major not = ‘Hist’ Can be rewritten as Select name from student EXCEPT Select name from student where Major = ‘Hist’ The answer is: Parks, Baker, Rusell and Rye.

  19. Querying multiple relations by using Subquery Suppose we need to know the names of students enrolled in the class BD445. If we know that only students 100 and 200 are enrolled in this class, then the following will produce the correct names. Select Name from Student Where Sid in [100, 200] => Jones Baker Select Student_number from Enrollment where Class_name = ‘BD445’ => 100 200 By combining the above 2 SQL statements, the SQL subquery can be easier to understand as follows: Select Name from Student where Sid in Select Student_number from Enrollment where Class_name=‘BD445’ => Jones Baker

  20. Suppose we want to know the names of the students enrolled in classes on Monday, Wednesday and Friday at 3 o’clock. First, we need the name of classes that meet at that time. Select Class.Name from Class where Time=‘MWF3’ => BD445 CS150 Now, what are the identifying numbers of relations in these classes? We can specify: Select Student_number from Enrollment where Enrollment.Class_name in Select Class_name from Class where Time = ‘MWF3’ => 100 200 300 Now, to obtain the names of those students, we specify: Select Student.name from Student where Student.Sid in Select Enrollment.Student_number from Enrollment where Enrollment.Class_name in Select Class_name from Class where Time = ‘MWF3’ => Jones Baker Baker

  21. Querying multiple relations using Join A join is the combination of a product operation, followed by a selection and a projection. For example, the From statement expresses the product of Student and Enrollment. The Where statement expresses the selection. The projection of student number, name and class name is taken. Select Student.Sid, Student.Name, Enrollment.Class_Name From Student, Enrollment Where Student.Sid=Enrollment.Student_Number => 100 Jones BD445 150 Parks BA200 200 Baker BD445 200 Baker CS250 300 Baker CS150 400 Rye BA200 300 Rye BF410 400 Rye CS250 450 Jones BA200 In this example, table Student and Enrollment are joined.

  22. Comparison of SQL Subquery and Join Join expressions can substitute for subquery expressions, the converse is not true. Subqueries cannot always be substituted for joins. When using a subquery, the displayed attributes can come from only the relation name in the from expression in the first select. For example, if we want to know the names of the students enrolled in classes on Monday, Wednesday, and Friday at 3 o’clock using Join. Select Student.Name from Student, Enrollment, Class Where Student.Sid = Enrollment.Student_Number and Enrollment.Class_Name = Class.Name and Class.Time = ‘MWF3’ If we want to know both the names of the classes and the grade levels of the undergraduate students, then we must use a join. A subquery will not suffice because the desired results arise from two different relations of Enrollment and Student.

  23. Exists and Not Exists Exists and Not Exists are logical operators; their value is either true or false depending on the presence of absence of tuples that fit qualifying conditions. For example, suppose we want to know the numbers of students enrolled in more than one class. The meaning of the subquery expression is “Find 2 tuples in enrollment having the same student number but different class names”. Select unique Student_number from Enrollment A where Exists Select * from Enrollment B where A.Student_number = B.Student_number and A.Class_name not = B.Class_name => 200 400

  24. Another example is that we want to know the names of students taking all classes. An expression is to say we want the names of students such that there are no classes that the student did not take. Select Student.Name from Student where not exists Select * from Enrollment where not exists Select * from Class where Class.Name = Enrollment.Class_name and Enrollment.Student_number = Student.Sid Note: This query has three parts. In the bottom part, we try to find classes the student did not take. The middle part determines if any classes were found that the student did not take. If not, then the student is taking all classes, and the student’s name is displayed.

  25. Inserting data Tuples can be inserted into a relation one at a time or in groups To insert a single tuple, we state Insert into Enrollment [400, ‘BD445’, 44] Or if we do not know all of this data, we could say Insert into Enrollment (Student_number, Class_name) [400, ‘BD445’] Note: If the student’s information is not available, this insertion will cause an referential integrity problem such that a child relation’s tuple can only be inserted if its corresponding parent relation’s tuple exists.

  26. Deleting data Tuples can be deleted one at a time or in groups. For example, we want to delete the tuple for student number 100 Delete Student where Student.Sid = 100 Note: If student 100 is enrolled in classes, this deletion will cause an referential integrity problem such that a parent relation’s tuple can be deleted only if its corresponding child relation’s tuple has been deleted. Another example is to delete groups of tuples. Delete Enrollment where Enrollment.Student_number IN Select Student.Sid from Student where Student.Major = ‘ACCT’ Delete Student where Student.Major = ‘ACCT’ Note: The order of these two deletion is important. If the order were reversed, none of the Enrollment tuples would be deleted because the matching Student tuples have already been deleted.

  27. Modifying data Tuples can be modified one at a time or in groups. The keyword Set is used to change an attribute value. After Set, the name of the attribute to be changed is specified and then the new value or way of computing the new value. Update Enrollment Set Position_number = 44 where Student_number = 400 Update Enrollment Set Position_number = Max(Position_number) + 1 where Student_number = 400 Note: the value of the attribute will be calculated using the Max built-in function. Another example for mass update is to change a course from BD445 to BD564. In this case, to prevent referential integrity problem, we perform as follows: alter table Enrollment Add Constraint FK foreign key (Class_name) references Class(Class_name) on update cascade; Update Enrollment Set Class_name = ‘BD564’ where Class_name = ‘BD445’ Update Class Set Class_name = ‘BD564’ where Class_name = ‘BD445’

  28. Database Programming Approaches • Embedded commands: • Database commands are embedded in a general-purpose programming language • Library of database functions: • Available to the host language for database calls; known as an API • API standards for Application Program Interface

  29. Embedded SQL in MS SQL Server Stored Procedure Stored Procedure: create proc show_course @incourse_no integer as select * from course where course_no = @incourse_no return Execution of Stored Procedure: execute show_course 4

  30. Embedded SQL • Most SQL statements can be embedded in a general-purpose host programming language such as COBOL, C, Java • An embedded SQL statement is distinguished from the host language statements by enclosing it between EXEC SQL or EXEC SQL BEGIN and a matching END-EXEC or EXEC SQL END (or semicolon) • Syntax may vary with language • Shared variables (used in both languages) usually prefixed with a colon (:) in SQL

  31. Example: Variable Declarationin Language C Variables inside DECLARE are shared and can appear (while prefixed by a colon) in SQL statements SQLCODE is used to communicate errors/exceptions between the database and the program int loop; EXEC SQL BEGIN DECLARE SECTION; varchar dname[16], fname[16], …; char ssn[10], bdate[11], …; int dno, dnumber, SQLCODE, …; EXEC SQL END DECLARE SECTION;

  32. Example for SQL Commands forConnecting to a Oracle Database In the “Microsoft ODBC for Oracle Setup” dialog, enter the following: Database Source Name: ora9i Description: <optional> Username: <optional> Server Name: w2ksc Start “Oracle -> OraHome90 -> Application Development -> SQL Plus User Name: grp## Password: abcd1234 Host String: w2ksc

  33. Example of a non-menu driven embedded SQL Stored Procedure to access an Oracle Database create procedure upd_per (in_id in integer) as begin update person set name = 'abc' where id_no = in_id; end; execute upd_per(1);

  34. Embedded SQL in CProgramming Examples loop = 1; while (loop) { prompt (“Enter SSN: “, ssn); EXEC SQL select FNAME, LNAME, ADDRESS, SALARY into :fname, :lname, :address, :salary from EMPLOYEE where SSN == :ssn; if (SQLCODE == 0) printf(fname, …); else printf(“SSN does not exist: “, ssn); prompt(“More SSN? (1=yes, 0=no): “, loop); END-EXEC }

  35. Embedded SQL in CProgramming Examples • A cursor (iterator) is needed to process multiple tuples • FETCH commands move the cursor to the next tuple • CLOSE CURSOR indicates that the processing of query results has been completed

  36. Embedded SQL in CProgramming Examples with Cursor Prompt (“Enter the Department Name:”, dname); EXEC SQL Select Dnumber into :dnumber from DEPARTMENT where Dname = :dname; EXEC SQL DECLARE EMP CURSOR FOR Selec Ssn, Fname, Minit, Lname, Salary from EMPLOYEE where Dno = :dnumber FOR UPDATE OF Salary; EXEC SQL OPEN EMP; EXEC SQL FETCH from EMP into :ssn, :fname, :minit, :lname, :salary; while (SQLCODE == 0) { printf (“Employee name is:”, Fname, Minit, Lname); prompt (“Enter the raise amount:”, raise); EXEC SQL update EMPLOYEE set Salary = Salary + :raise where CURRENT OF EMP; EXEC SQL FETCH from EMP into :ssn, :fname, :minit, :lname, :salary; } EXEC SQL CLOSE EMP;

  37. Lecture Summary SQL is a high level relational database language. It is much user friendly than other database languages, and becomes the most popular database language since 90’s. Nevertheless, it is not an end user language such as Natural language, and is subject to be changed by individual relational database vendors and academic committee.

  38. Review Question What are the difference between Query and Nested Query in SQL with respect to performance and why? How does Entity Integrity affect an Insert operation of SQL? How does Referential Integrity affect Insert and Update operations of SQL? What is the condition for using UNION, INTERSECT and EXCEPT clauses in SQL?

  39. Tutorial Question Given the following relations, write SQL statements to answer the questions below: Customer (Customer_id, Customer_name, Customer_Address) Order (Order_id, *Customer_id, Date, Cost) Payment (Payment_id, *Customer_id, Date, Amount) (a) List out all the customer names who paid more than $300 on the 15th March 1997. • without using sub-query (30%) • using sub-query (30%) (b) Remove a customer with customer_id ######## from the customer list where ######## is unique student id in 8 digits (40%)

  40. Reading Assignment Chapter 8 SQL-99: Schema Definition, Constraints, Queries, and Views “Fundamentals of Database Systems” by Elmasri & Navathe fifth edition, Pearson, 2007.

More Related