1 / 119

CS 328 Database Systems Chapter 8: SQL

CS 328 Database Systems Chapter 8: SQL. Introduction. Full name of SQL --- S tructural Q uery L anguage Old name --- SEQUEL (Structural English QUEry Language) First developed at IBM's San Jose (now Almaden) Research Lab. in 1974. Over 100 SQL-based products in the market.

karlyn
Download Presentation

CS 328 Database Systems Chapter 8: 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. CS 328Database SystemsChapter 8: SQL

  2. Introduction • Full name of SQL --- Structural Query Language • Old name --- SEQUEL (Structural English QUEry Language) • First developed at IBM's San Jose (now Almaden) Research Lab. in 1974. • Over 100 SQL-based products in the market. • E.g.: Oracle, Sybase, Ingres, Informix, DB2.

  3. Introduction • SQL is a data manipulation language. • SQL is not a programming language. • SQL commands are interpreted by the DBMS engine. • SQL commands can be used interactively as a query language within the DBMS. • SQL commands can be embedded within programming languages.

  4. Introduction SQL Concept Relational Concept Table Relation Row Tuple Column Attribute

  5. Types of SQL Commands • Data Definition Language (DDL): • Commands that define a database - Create, Alter, Drop • Data Manipulation Language (DML) • Commands that maintain and query a database. • Data Control Language (DCL) • Commands that control a database, including administering privileges and committing data.

  6. 8.1: SQL DDL & Types • Catalog: A set of user schemas that typically constitute a description of a database • A catalog can contain many user schemas. • Schema: A collection of database objects created by a user, such as base tables, views, and constraints. • A schema is created via the CREATE SCHEMA statement which should include at least the schema name and authorization identifier. • CREATE SCHEMA Company Authorization JSmith;

  7. 8.1.2: The CREATE TABLE Command • Create the following relation: • Student(SSN, Name, GPA, Age). • SQL> create table Student ( 2 SSN char(9) not null, 3 Name varchar2(20), 4 GPA number(3,2), 5 Age number(2), 6 primary key (SSN));

  8. 8.1.3: Attribute Data Types in SQL (1) • char(n) • fixed length character string of length n • n < 2000 • varchar2(n) • variable length character string of length up to n • n <= 4000 • long • variable length character string of length up to 2GB • number(n, m) • float point of n digits, of which m digits are after the decimal point • n >= m >= 0

  9. 8.1.3: Attribute Data Types in SQL (2) • number(n) • integer and real values of up to n digits • number • integer and real values of up to 40 digits • integer(n) • integer of up to n digits • integer • integer of up to 40 digits • date • yyyy-mm-dd (10 positions) • time • typically in the form HH:MM:SS

  10. Domain Definitions • CREATE DOMAIN <name> As <data-type>; • CREATE DOMAIN SSN-TYPE AS CHAR(9); • We can use SSN_TYPE in place of CHAR(9) • This style in writing SQL statements is preferred as it makes it easier to change the data types of attributes. • A domain can also have an optional default specification via default clause. • CREATE DOMAIN DNO-DOM AS CHAR(2) DEFAULT 01;

  11. 8.2: Specifying Constraints in SQL • Constraints are rules or regulations imposed on data to ensure their integrity and correctness. • Constraints on individual columns. • Constraints on a table. • Constraints on multiple tables.

  12. 8.2.1: Constraints on Columns (1) Create table Book( ISBN char(15) primary key, Title varchar2(50) unique, Price number(5,2) check (Price>0), Edition number(2) default 1 check (Edition > 0), Publisher_name varchar2(30) references Publisher(Name));

  13. Constraints on Individual Columns (2) • type • values must be of the given type • not null • no null value can be taken • unique • no identical non-null values • primary key • no null value and no identical values • check constraint definition • values must satisfy the check condition.

  14. Constraints on Individual Columns (3) • Syntax for column definition: col_name data_type [default value] [constraints] • Syntax for column constraints: [constraint constraint_name] [not] null | check condition | unique | primary key | referencestable_name [(column)] [on delete cascade] [on update cascade]

  15. Create Table Example (4) Create table Enrollment( SSN char(9) references Students, Crs_no varchar2(7) not null, Section_no number(2) not null, Grade char, primary key(SSN,Crs_no,Section_no), foreign key (Crs_no,Section_no) references Section);

  16. 8.2.3: Giving Names to Constraints • Syntax for table constraints: [constraint constraint_name] check condition | unique (column {, column}) | primary key (column {, column}) | foreign key (column {, column}) references table_name[(column{, column)]

  17. 8.2.3: Giving Names to Constraints Create table Graduate( SSN char(9) primary key, Name varchar2(30), Address varchar2(80), Degree varchar2(8) check (Degree in {‘Bachelor’, ‘Master’, ‘Ph.D.’}), GPA number (3,2) not null, unique (Name, Address), constraint ic12check (GPA>=2.0 and (Degree=‘Bachelor’ or GPA>=3.0)));

  18. Disabling and Enabling a Constraint • Disabling a constraint: • alter table Graduate disable constraint ic12; • Enabling a constraint: • alter table Graduate enable constraint ic12;

  19. 8.3.1: The Drop Command • DROP SCHEMA COMPANY CASCADE; • the schema and all its elements (tables, views, domain definitions … etc.) are also deleted. • DROP SCHEMA COMPNAY RESTRICT; • the schema will be deleted if it has no elements. If it has any element the drop schema statement will not be executed. • DROP TABLE DEPENDENT CASCADE; • The table dependent and all other elements that reference the dependent table are also deleted (e.g. foreign keys and views). • DROP TABLE DEPENDENT RESTRICT; • The table is deleted only if it is not referenced in any constraint (foreign key definitions in other tables).

  20. Delete a Table • drop table Dept; • If Departments is being referenced by other tables (e.g., foreign key references), then this statement will fail. • drop table Dept cascade constraints; • If Departments is being referenced by other tables, then all referential constraints will be dropped before the table itself is dropped.

  21. Truncate a Table • truncate table Department; • It removes all rows from a table and release the storage space used by the table. • delete from Department; • It removes all rows from a table but does not release the storage space used by the table.

  22. 8.3.2: ALTER TABLE • Add attributes to base relations: • ALTER TABLE COMPANY.EMPLOYEE ADD column JOB VARCHAR(12); • Drop a column: • ALTER TABLE COMPANY.EMPLOYEE DROP column ADDRESSCASCADE; • all views and constraints that reference this column are automatically deleted. • ALTER TABLE COMPANY.EMPLOYEE DROP column ADDRESSRESTRICT; • the address attribute won’t be deleted if it is being referenced by other schema elements.

  23. ALTER TABLE • Drop the default value of an attribute: • ALTER TABLE COMPNAY.DEPARTMENT alter column MGRSSNDROPDEFAULT; • Change the default value of an attribute: • ALTER TABLE COMPANY.DEPARTMENT ALTER column MGRSSNSET DEFAULT “333445555”; • Drop constraints • ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFKCASCADE; • Add constraints • ALTER TABLE COMPANY.EMPLOYEE ADD CONSTRAINT ……;

  24. ALTER TABLE • alter table Student add column Address varchar2(40); • alter table Student add constraint unique(Address); • not null cannot be specified for newly added attributes.

  25. DDL Summary • create table • alter table … add | modify • drop table • truncate table • rename table1 to table2 • DDL statements, once executed, are irreversible!NO UNDO!!!

  26. 8.4: Basic Queries in SQL • Relation schemas under consideration: • Student (SSN, Name, GPA, Age) • Course (Course_No, Title, Dept_Name) • Enrollment (SSN, Course_No, Grade)

  27. The SELECT-FROM-WHERE (1) Select target-attribute-list From table-list Where conditions • Relational algebra correspondence: • select-clause projection () • from-clause Cartesian product () • where-clause selection ()

  28. The SELECT-FROM-WHERE (2) • Find the SSN, Name and GPA of all students whose GPA is higher than 3.8. Select SSN, Name, GPA From Students Where GPA > 3.8; • Select all EMPLOYEE SSNs. SELECT SSN FROM EMPLOYEE; • Select all combinations of Employee SSNs and Department Dname. SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT;  SSN, DNAME(EMPLOYEE x DEPARTMENT)

  29. The SELECT-FROM-WHERE (3) • Select all attributes of employees who work for department number 5; SELECT * FROM EMPLOYEE WHERE DNO = 5; • Retrieve the birth-date and address of the employee whose name is “John b. Smith” SELECT BDate, Address FROM Employee Where Fname = ‘John’ AND Minit = ‘B’ AND Lname = ‘Smith’; •  Bdate, address ( Fname= “Jone” and Minit = “B” and Lname = “Smith (Employee))

  30. The SELECT-FROM-WHERE (4) • Retrieve the name and address of all employees who work for the research department. SELECT Fname, Lname, Address FROM Employee, Department WHERE Dname=“Research” and Dnumber=DNO; • Find the names and GPAs of all students who take database systems. Select Name, GPA From Student, Enrollment, Course Where Title = `database systems` and Students.SSN = Enrollment.SSN and Enrollment.Course_no=Courses.Course_no;

  31. The SELECT-FROM-WHERE (5) • The semantics of the previous query can be expressed (almost correctly!) as: Name,GPA(Title=`database systems’ and Students.SSN=Enrollment.SSN and Enrollment.CNo = Courses.CNo (Student  Enrollment  Course)) Name, GPA (Student (Enrollment  Title = `database systems'(Course))) • In general, select distinct R1.A, R2.B, ..., Rn.C from R1, R2, ..., Rn where Conditions is equivalent to R1.A, R2.B, ..., Rn.C (Conditions (R1R2...  Rn))

  32. 8.4.2: Ambiguous Attribute Names, Aliasing • In SQL the same name can be used for several attributes as long as they appear in different relations. When such attributes appear in a single query, they should be qualified with their corresponding relation name. EMPLOYEE(SSN, Name, …., DNo) DEPARTMENT(DNo, Name, …) SELECT SSN, Employee.Name FROM Employee, Department WHERE Department.Name = “Research” and Department.DNo = Employee.DNo;

  33. Dealing with Aliasing (1) • For each employee retrieve the employee’s first and last name and the first and last name of his or her immediate advisor. SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM Employee E, Employee S WHERE E.SuperSSN = S.SSN; • We may write Employee AS E • Note that aliases can be used to shorten queries by giving short names to relations.

  34. Dealing with Aliasing (2) • We may alias attributes as well. • Employee(Name, Address, Supervisor, Dept) • Q1: • Select NA, Adr, S, Dept FROM EMPLOYEE AS E(NA, Adr, S, D) • Or this • Select Name NA, Address adr, Supervisor S, Dept D FROM EMPLOYEE; • Find the names and GPAs of all students who take database systems. Select Name, GPA From Student S, Enrollment E, Course C Where Title=`database systems‘ and S.SSN=E.SSN and E.Course_no=C.Course_no

  35. Dealing with Aliasing (3) • Find all pairs of students who have the same GPA. select s1.SSN, s2.SSN from Student s1, Student s2 where s1.GPA=s2.GPA and s1.SSN<s2.SSN; • Question: • Why use “s1.SSN < s2.SSN”?

  36. Dealing with Aliasing (4) • Find the names of all students whose GPA is higher than Tom's GPA. select s1.Name from Students s1, Students s2 where s2.Name=‘Tom’ and s1.GPA>s2.GPA; • Question: Is a student qualified if his/her GPA is higher than some Tom's GPAs but not all Tom's GPAs?

  37. Renaming SELECT Attributes • SQL allows us to rename SELECT attributes • This does not significantly change the results of a query, but it does allow us to control the headings of the columns displayed on reports SELECT FNAME AS FN, LNAME AS LN FROM EMPLOYEE WHERE SUPERSSN IS NULL • The same tuples will be selected as for the last query, but now the columns will be labeled FN and LN instead of FNAME and LNAME.

  38. 8.4.3: Unspecified Where Clause & Use of Asterisk • To retrieve all attributes of a relation, use the shorthand * • To retrieve all the attribute values of the selected tuples, a * is used, which stands for all the attributes • Find all students whose GPA is higher than 3.8. select * from Students where GPA > 3.8; • The where-clause may be absent. • Find the Names of all students. select Name from Students; • Select all EMPLOYEE SSNs. SELECT SSN FROM EMPLOYEE;

  39. Unspecified Where clause • A missing WHERE-clause indicates no condition; hence, all tuples of the relations in the FROM-clause are selected • This is equivalent to the condition WHERE TRUE • Retrieve the SSN values for all employees. SELECT SSN FROM EMPLOYEE; • If more than one relation is specified in the FROM-clause and there is no join condition, then the CARTESIAN PRODUCT of tuples is selected

  40. Use of Asterisk • Retrieve all the attributes of an employee and the attributes of the department he/she works in for every employee of the “Research” department. SELECT * FROM Employee, Department WHERE Dname = “Research” AND DNO = Dnumber; • Specify (retrieve) the cross product of the Employee and Department relations; SELECT * FROM Employee, Department; • Select all combinations of Employee SSNs and Department Dname. SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT;  SSN, DNAME(EMPLOYEE x DEPARTMENT)

  41. 8.4.4: Tables as Sets in SQL (1) • SQL does not treat relations as sets, i.e. duplicate tuples can appear more than once in a relation or the result of query. • Why SQL does not eliminate duplicate tuples? • Duplicate elimination is an expensive operation, one way to do it is to sort the tables. • Users may want to see duplicate tuples in query results • when an aggregate function is applied to tuples, in most cases we do not want to eliminate duplicates. • To remove duplicate rows, select distinct should be used.

  42. Tables as Sets in SQL (2) • Find the Names of all students without duplicate rows. Select distinct Name from Student; • Select all can be used to explicitly request that all duplicate rows are kept. • Is the following query an efficient query? select distinctSSN, Name from Student • Retrieve the salary for every employee. • duplicates may appear SELECT Salary FROM Employee; • duplicates will be eliminated SELECT DISTINCT Salary FROM Employee;

  43. Union, Intersection, and Except (1) • SQL supports three set operations: • union, intersect, minus • Union compatibility is required. • Consider the following two relations: • Students(SSN, Name, GPA, GRE, DeptName) • Instructors(SSN, Name, Office, DeptName)

  44. Union, Intersection, and Except (2) • Find the names of those people who are either a graduate student or an instructor or both in CS department. select Name from Student where DeptName='CS' union select Name from Instructor where DeptName='CS' • union removes duplicate rows. • union all keeps duplicate rows.

  45. Union, Intersection, and Except (3) • Find the names of those who are both a graduate student and an instructor in CS department. select Name from Student where DeptName='CS‘ intersect select Name from Instructor where DeptName='CS‘ • Find the names of those who are an instructor but not a graduate student in CS department. select Name from Instructor where DeptName='CS' except select Name from Student where DeptName='CS'

  46. Union, Intersection, and Except (5) • make a list of all project numbers for projects that involve an employee whose last name is “Smith”, either as a worker or as a manager of the department that controls the project. (SELECT Distinct Pnumber FROM PROJECT, Works-on, Employee WHERE Pnumber = Pno AND ESSN = SSN AND Lname = “Smith”) UNION (SELECT PNumber FROM Project, Department, Employee WHERE Dnum=Dnumber AND MGRSSN=SSN AND Lname=“Smith”)

  47. 8.4.5: Substring Pattern Matching (1) • Find the course numbers and titles of all courses whose title contains “systems”. select Course_no, Title from Courses where Title like `%systems%`; • % matches 0 or more characters.

  48. Substring Pattern Matching (2) • Find all students whose name starts with M and is six-character long. select * from Students where Name like `M_ _ _ _ _`; • _ matches exactly one character • not like is the opposite of like. • Find all students whose name contains a _. select * from Students where Name like `%\_ %`;

  49. Substring Pattern Matching (3) • Retrieve all employees whose address in Housten, Texas. SELECT Fname, Lname FROM Employee WHERE Address LIKE ’%Houston, TX%’; • Find all employees who were born during the 1950s. SELECT Fname, Lname FROM Employee WHERE Bdate LIKE ’195_ _ _ _ _’;

  50. Arithmetic Operators (1) • Show resulting salaries if every employee working on the ‘productX’ project is given a 10% raise. SELECT Fname, Lname, 1.1*Salary FROM Employee, Works_on, Project WHERE SSN = ESSN AND PNO = Pnumber AND Pname=“ProductX”; • Find the names of all students whose GPA is between 3.5 and 3.8, inclusive. select Name from Students where GPA between 3.5 and 3.8; • not between ... and … is the opposite of between … and ...

More Related