1 / 66

Database Systems Basic SQL

Database Systems Basic SQL. Outline. SQL Data Definition and Data Types Specifying Constraints in SQL Basic Retrieval Queries in SQL INSERT, DELETE, and UPDATE Statements in SQL Additional Features of SQL. Basic SQL. SQL language

chibale
Download Presentation

Database Systems Basic 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. Database SystemsBasic SQL

  2. Outline • SQL Data Definition and Data Types • Specifying Constraints in SQL • Basic Retrieval Queries in SQL • INSERT, DELETE, and UPDATE Statements in SQL • Additional Features of SQL

  3. Basic SQL • SQL language • Considered one of the major reasons for the commercial success of relational databases • SQL • Structured Query Language • Statements for data definitions, queries, and updates (both DDL and DML) • SQL is not a programming language. • SQL commands can be used interactively as a query language within the DBMS or SQL commands can be embedded within programming languages.

  4. 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. • Each statement in SQL ends with a semicolon

  5. SQL Concept & Relational Concept • Terminology: • Table, row, and column used for relational model terms relation, tuple, and attribute SQL Concept Relational Concept Table Relation Row Tuple Column Attribute

  6. The CREATE TABLE Command • CREATE statement • Main SQL command for data definition • Specify a new relation • Provide name • Specify attributes and initial constraints • Base tables (base relations) • Relation and its tuples are actually created and stored as a file by the DBMS

  7. The CREATE TABLE Command • Can optionally specify schema: • CREATE TABLE COMPANY.EMPLOYEE ... or • CREATE TABLE EMPLOYEE ... • Create the following relation: • Student(SSN, Name, GPA, Age). • SQL> create table Student ( 2 SSN char(9) not null, 3 Name varchar(20), 4 GPA number(3,2), 5 Age number(2), 6 primary key (SSN));

  8. Attribute Data Types char(n)fixed length character string of length n varchar(n) variable length characters of length up to n number(n,m)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 intege r integer of up to 40 digits date yyyy-mm-dd (10 positions) time typically in the form HH:MM:SS

  9. 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;

  10. 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.

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

  12. Constraints on Individual Columns (2) • type values must be of the given type • not null no null value can be taken • unique • Specifies alternate (secondary) keys • Dname VARCHAR(15) UNIQUE; • no identical non-null values • primary key • Specifies one or more attributes that make up the primary key of a relation • no null value and no identical values • Dnumber INT PRIMARY KEY; • check constraint definition • values must satisfy the check condition. • Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);

  13. 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]

  14. Create Table Example (4) Create table Enrollment( SSN char(9) references Students, Crs_novarchar(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);

  15. 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)] • CHECK clauses at the end of a CREATE TABLE statement • Apply to each tuple individually • CHECK (Dept_create_date <= Mgr_start_date);

  16. Giving Names to Constraints Create table Graduate( SSN char(9) primary key, Name varchar(30), Address varchar(80), Degree varchar(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)));

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

  18. Basic Retrieval Queries in SQL SELECT statement One basic statement for retrieving information from a database

  19. The SELECT-FROM-WHERE Basic form of the SELECT statement:

  20. The SELECT-FROM-WHERE Structure • Logical comparison operators • =, <, <=, >, >=, and <> • Projection attributes • Attributes whose values are to be retrieved • Selection condition • Boolean condition that must be true for any retrieved tuple

  21. The SELECT-FROM-WHERE • 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;

  22. The SELECT-FROM-WHERE • Select all attributes of employees who work for department number 5; SELECT * FROM EMPLOYEE WHERE DNO = 5; • 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;

  23. Ambiguous Attribute Names Same name can be used for two (or more) attributes As long as the attributes are in different relations Must qualify the attribute name with the relation name to prevent ambiguity EMPLOYEE(SSN, Name, …., DNo) DEPARTMENT(DNo, Name, …)

  24. Aliasing, Renaming, and Tuple Variables Aliases or tuple variables Declare alternative relation names E and S EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Sssn, Dno)

  25. 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.

  26. 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

  27. 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”?

  28. 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?

  29. 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.

  30. Unspecified WHERE Clauseand Use of the Asterisk Missing WHERE clause Indicates no condition on tuple selection CROSS PRODUCT All possible tuple combinations

  31. Unspecified WHERE Clauseand Use of the Asterisk (cont’d.) Specify an asterisk (*) Retrieve all the attribute values of the selected tuples

  32. 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;

  33. 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

  34. 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;

  35. Tables as Sets in SQL SQL does not automatically eliminate duplicate tuples in query results Use the keyword DISTINCT in the SELECT clause Only distinct tuples should remain in the result 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;

  36. Tables as Sets in SQL (cont’d.) Set operations UNION, EXCEPT (difference), INTERSECT Corresponding multiset operations: UNIONALL, EXCEPTALL, INTERSECTALL) union removes duplicate rows. union all keeps duplicate rows.

  37. Substring Pattern Matching and Arithmetic Operators LIKEcomparison operator Used for string pattern matching % replaces an arbitrary number of zero or more characters underscore (_) replaces a single character Standard arithmetic operators: Addition (+), subtraction (–), multiplication (*), and division (/) BETWEEN comparison operator

  38. Substring Pattern Matching • 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.

  39. Substring Pattern Matching • 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 `%\_ %`;

  40. Substring Pattern Matching • 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_ _ _ _ _’;

  41. Arithmetic Operators • 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 ...

  42. Arithmetic Operators • Employees (SSN, Name, Salary) • Find the SSN, name and annual salary of each employee. select SSN, Name, 12*Salary from Employees; or select SSN, Name,12*Salary as Annual_Salary from Employees;

  43. Ordering of Query Results Use ORDER BYclause Keyword DESCto see result in a descending order of values Keyword ASCto specify ascending order explicitly ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC

  44. Ordering Tuples • Find the names of all students and order the names in ascending order. select Name from Students order by Name asc; • ascending order is the default in order by clause. • We can write: order by Dname desc, Lname ASC, Fname ASC

  45. Ordering Tuples • Find all the students whose GPA is higher than 3.5 and order the result in descending order by GPA, and for students having the same GPA, order them in ascending order by their names. select * from Students where GPA > 3.5 order by GPA desc, Name asc;

  46. Ordering Tuples • Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, alphabetically by last name, first name; SELECT Dname, Lname, Fname, Pname FROM Department, Employee, Works_on, Project WHERE Dnumber=DNO AND SSN=ESSN AND PNO=Pnumber ORDER BY Dname, Lname, Fname;

More Related