Chapter 8 SQL: Schema Definition, Basic Constraints, and Queries

Chapter 8 SQL: Schema Definition, Basic Constraints, and Queries Create database Syntax: Example:  CREATE DATABASE QuanlySach CREATE DATABASE database_name Create database There are a number of optional arguments that you can supply with the CREATE DATABASE command: –Logic name –Physical name –Size: initial size. –Maximum size. –Growth increment Create database CREATE DATABASE Logical_database_name ON ( PRIMARY ( NAME = logical_file_name, FILENAME = ‘path\filename.mdf', SIZE = size, MAXSIZE = maxsize, FILEGROWTH = filegrowth_increment ) LOG ON ( NAME = logical_file_name, FILENAME = ‘path\filename.ldf', SIZE = size, MAXSIZE = maxsize, FILEGROWTH = filegrowth_increment ) Create database Example: CREATE DATABASE QuanlySach ON PRIMARY ( NAME = QuanlySach, FILENAME = ‘D:\data\QuanlySach.mdf', SIZE = 10 MB, MAXSIZE = 40 MB, FILEGROWTH = 1 MB) LOG ON ( NAME = SalesDB_log, FILENAME = ‘D:\data\QuanlySach.ldf', SIZE = 6 MB, MAXSIZE = 8 MB, FILEGROWTH = 5% ) Create table Specifies a new base relation by giving it a name, and specifying each of its attributes and their data types (INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n)) A constraint NOT NULL may be specified on an attribute Syntax: CREATE TABLE table_name (column_name_1 datatype, column_name_2 datatype, ... ) Create table Example: CREATE TABLE Nhanvien (MaNhanvien char(5), HoNhanvien Varchar(30), TenNhanvien Varchar(25), Ngaysinh Smalldatetime ) Attribute Data Types and Domains in SQL Numeric:  Integer numbers of various sizes:  INTEGER, INT, SMALLINT.  Floating-point (real) numbers of various precision:  FLOAT or REAL and DOUBLE PRECISION).  Formatted numbers can be declared by using DECIMAL(i, j)-or DEC(i, j) or NUMERIC(i, j)  i: is the total number of decimal digits.  J: is the number of digits after the decimal point. The default is zero. Attribute Data Types and Domains in SQL Character-string:  CHAR(n) or CHARACTER(n).  n is the number of characters-or varying length-VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(n)  n is the maximum number of characters. Bit-string: fixed length n-BIT(n)-or varying length- BIT VARYING(n), where n is the maximum number of bits. The default for n, the length of a character string or bit string, is 1. Attribute Data Types and Domains in SQL A Boolean: has the traditional values of TRUE or FALSE. In SQL, because of the presence of NULL values, a three-valued logic is used, so a third possible value for a Boolean data type is UNKNOWN. We discuss the need for UNKNOWN. Date and time were added in SQL2. The DATE data type has ten positions, and its components are YEAR, MONTH, and DAY in the form YYYY-MM-DD. The TIME data type has at least eight positions, with the components HOUR, MINUTE, and SECOND in the form HH:MM:SS Data integrity(toàn vẹn dữ liệu) Data integrity: ensures the quality of the data in the database.  For example, if an employee is entered with an employeeID value of 123, the database should not allow another employee to have an ID with the same value. There are two techniques to ensure Data integrity:  Procedural integrity (trigger, stored procedure) Declarative integrity: they are attributes of the database or table (contraint, default, rule) Declarative integrity Entity Integrity: defines a row as a unique entity for a particular table.  Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table. Domain Integrity: is the validity of entries for a given column. You can enforce domain integrity by: Data types The format (through CHECK constraints and rules), The range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT, NOT NULL, rules). Declarative integrity Referential Integrity: preserves the defined relationships between tables when records are entered or deleted.  Referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys. Referential integrity: prevents users from:  Adding records to a related table if there is no associated record in the primary table.  Changing values in a primary table that result in orphaned records in a related table.  Deleting records from a primary table if there are matching related records. Declarative integrity Default constraint:  Define a Default constraint when creating table Define a Default constraint when existing table: ALTER TABLE Table_name ADD [CONSTRAINT Constraint_name] DEFAULT expression FOR column_name CREATE TABLE Table_name (Column_name Datatype [NULL| NOT NULL] [CONSTRAINT Constraint_name] DEFAULT expression[]) Declarative integrity Example:  CREATE TABLE events ( EventID int Indentity(1, 1) Not Null, EventType nvarchar(10) Not Null, EventTitle nvarchar(100) Null, EventDate SmallDatetime Null Default Getdate() )  ALTER TABLE events ADD DEFAULT ‘party’ for EventType Declarative integrity Delete Default constraint: Example: ALTER TABLE Orders DROP CONSTRAINT DF_Orders_OrderDate ALTER TABLE Table_name DROP CONSTRAINT Constraintname Declarative integrity Primary Key Constraints: A primary key is a column or set of columns that can be used to uniquely identify a row in a table.  Syntax: Creating a Primary Key When Creating a Table, A primary key is a column CREATE TABLE table_name ( column_name data_type NOT NULL [CONSTRAINT constraintname] PRIMARY KEY ) Declarative integrity  Syntax: Creating a Primary Key When Creating a Table, A primary key is a set of column. CREATE TABLE table_name ( column_name data_type[,] [CONSTRAINT constraintname] PRIMARY KEY{(column1[ASC|DESC][,columnN])} ) Declarative integrity Example:  CREATE TABLE Hoadon ( MaHD int NOT NULL CONSTRAINT PK_HoaDon PRIMARY KEY );  CREATE TABLE Ketqua ( masv char(10) not null, mamh varchar(40) not null, Diem float not null, Primary key (masv, mamh) ); Declarative integrity  Syntax: Creating a Primary Key When existing a Table, A primary key is a column  Syntax: Creating a Primary Key When existing a Table, A primary key is a set of column ALTER TABLE table_name ADD [CONSTRAINT constraint_name ] PRIMARY KEY {(column[ASC|DESC][,columnN])} ALTER TABLE table_name ADD Column_name datatype [CONSTRAINT constrain_name] PRIMARY KEY Declarative integrity Remove a Primary Key Constraint: ALTER TABLE table_name DROP CONSTRAINT Constraint_name Declarative integrity Unique Constraints: You can use a unique constraint to maintain distinct values in a column or set of columns that do not participate in the primary key.  Can define multiple unique constraints per table.  Can define a unique constraint on one or more columns that accept NULL values However, if define a unique constraint on single column, that column can accept only one NULL value. Declarative integrity  Syntax: Creating a Unique Constraint When Creating a Table (one column)  Example: CREATE TABLE Orders (OrderID int NOT NULL CONSTRAINT PK_ORDERS PRIMARY KEY, OrderNumber int NULL CONSTRAINT UQ_ORDER_NUMBER UNIQUE) CREATE TABLE table_name ( column_name data_type [CONSTRAINT constraint_name] UNIQUE ) Declarative integrity  Syntax: Creating a Unique Constraint When Creating a Table (many columns) CREATE TABLE table_name ( columnname datatype[,] [CONSTRAINT constraint_name] UNIQUE {(column[ASC|DESC][,columnN])} ) Declarative integrity  Syntax: Create a Unique Constraint Using the ALTER TABLE Statement. Remove a unique constraint from a table: ALTER TABLE table_name ADD [CONSTRAINT constraint_name] UNIQUE [CLUSTERED | NONCLUSTERED ] (column_name) ALTER TABLE table_name DROP CONSTRAINT constraint_name Declarative integrity Foreign Key Constraints: to enforce a relationship between the data in two tables. Syntax Creating a Foreign Key Constraint when creating Tables CREATE TABLE table_name ( colum_name datatype [,...], [CONSTRAINT constraint_name ] FOREIGN KEY [ ( column [ ,...n ] ) ] REFERENCES ref_table [ ( ref_column [ ,...n ])] ) Declarative integrity Create table Phongban (Mapb int, Tenpb varchar(30), Constraint pb_PK primary key (mapb) ) Create table nhanvien (manv int, Hoten varchar(40), Mapb int, Constraint manv_PK primary key (manv), Contraint mapb_Fk foreign key(mapb) references phongban(mapb) ) Declarative integrity Check Constraints: help enforce domain integrity by validating or checking the data that is being inserted into a column before accepting the value. There may be multi check constraint in a column. Syntax: Create a Check Constraint When Creating a Table CREATE TABLE table_name (column_name data_type [CONSTRAINT constraint_name] CHECK (logical expression) Declarative integrity Example: CREATE TABLE nhanvien ( manv smallint PRIMARY KEY CLUSTERED, tennv varchar(50) NOT NULL , tuoimin tinyint NOT NULL CHECK (tuoimin >= 18), tuoimax tinyint NOT NULL CHECK (tuoimax <= 40) ) Declarative integrity Syntax: Create a Check Constraint Using the ALTER TABLE Statement ALTER TABLE table_name ADD [CONSTRAINT constraint_name] CHECK (logical expression) Referential integrity options RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential integrity constraints (foreign keys) Syntax: CREATE TABLE table_name ( colum_name datatype [,...], [CONSTRAINT constraint_name ] FOREIGN KEY [ ( column [ ,...n ] ) ] REFERENCES ref_table [ ( ref_column [ ,...n ])] ON DELETE SET DEFAULT ON UPDATE CASCADE ) Referential integrity options Example: Create table nhanvien (manv int, Hoten varchar(40), Mapb int, Constraint manv_PK primary key (manv), Contraint mapb_Fk foreign key(mapb) references phongban(mapb) on delete set default on update cascade ); Basic queries in SQL The SELECT statement: SELECT [DISTINCT] select_list [INTO new_table ] FROM table_source [WHERE search_condition ] [GROUP BY group_by_expression ] [HAVING search_condition] [ORDER BY order_expression [ASC|DESC ] ] Basic queries in SQL  [DISTINCT]: remove all duplicate rows from query results.  Example: SELECT DISTINCT Cust.CustomerID, CompanyName FROM Customers AS Cust INNER JOIN Orders AS Ord ON Cust.CustomerID = Ord.CustomerID ORDER BY Cust.CustomerID Basic Queries in SQL  [WHERE]: is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query. Example: 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 Basic Queries in SQL  [GROUP BY]: to apply the aggregate functions to subgroups of tuples in a relation, where the subgroups are based on some attribute values.  Example: find the average salary of employees in each department or the number of employees who work on each project. SELECT DNa, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNa; Basic Queries in SQL Basic Queries in SQL  [HAVING]: to restrict conditionally the output of a SQL statement, by a SQL aggregate function.  Example: For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project. SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2; Basic Queries in SQL Basic Queries in SQL  [ORDER BY]: to sort your SQL result sets by some column(s).  Example: SELECT c.CustomerID, COUNT(o.orderID) Total Orders’, SUM (od.UnitPrice * od.Quantity) ‘Total Sales’ FROM Customers c, Orders o, [Order Details] od WHERE c.CustomerID = o.CustomerID AND o.OrderID = od.OrderID GROUP BY c.CustomerID Basic Queries in SQL Aliasing: In SQL the same name can be used for two (or more) attributes as long as the attributes are in different relations, a query refers to two or more attributes with the same name, we must qualify the attribute name with the relation name to prevent ambiguity.  SQL column aliases are used to make the output of your SQL queries easy to read and more meaningful Basic Queries in SQL Example: For each employee, retrieve the employee's first and last name and the first and last name of his or her immediate supervisor. SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE AS E, EMPLOYEE AS S WHERE E.SUPERSSN=S.SSN Basic Queries in SQL SQL JOIN:  INNER JOIN: return rows when there is at least one match in both tables.  Syntax: SELECT col_name(s) FROM table1 INNER JOIN table2 ON table1.col_name=table2.col_name Basic Queries in SQL Example: SELECT Customers.CustomerID, CompanyName, OrderID, OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID Basic Queries in SQL OUTER JOIN:  LEFT OUTER JOIN: returns all rows from the left table, even if there are no matches in the right table. RIGHT OUTER JOIN: returns all records from the right table even no matching record found in the left table, plus matching records in the table A. Basic Queries in SQL  Syntax: SELECT colname(s) FROM FROM table1 {LEFT OUTER JOIN | LEFT OUTER JOIN} table2 ON table1.colname=table2.colname Basic Queries in SQL CROSS JOIN: return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables. SELECT column_list FROM table1 CROSS JOIN table2 Basic Queries in SQL Example: SELECT makh, tenkh, honv+' '+tennv AS TenNV FROM Khachhang CROSS JOIN nhanvien Insert, delete, and update The INSERT Command: Example: Insert into employee (fname, lname, dno, ssn) VALUES ('Richard', 'Marini', 4, '653298653'); INSERT [INTO] [(Col1, Col2, )] VALUES (Value1, Value2,) Insert, delete, and update A variation of the INSERT command inserts multiple tuples into a relation in conjunction with creating the relation and loading it with the result of a query.  Example: CREATE TABLE DEPTS_INFO ( DEPT_NAME VARCHAR(15), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER ); Insert, delete, and update Example(tt) INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS,TOTAL_SAL) SELECT DNAME, COUNT (*), SUM (SALARY) FROM (DEPARTMENT JOIN EMPLOYEE ON DNUMBER=DNO) GROUP BY DNAME; Insert, delete, and update The DELETE Command: removes tuples from a relation. It includes a WHERE clause, similar to that used in an SQL query, to select the tuples to be deleted. Syntax: Example: DELETE FROM EMPLOYEE WHERE LNAME='Brown'; DELETE [FROM] [FROM ] [WHERE Insert, delete, and update The UPDATE Command: to modify attribute values of one or more selected tuples.  a WHERE clause in the UPDATE command selects the tuples to be modified from a single relation. However, updating a primary key value may propagate to the foreign key values of tuples in other relations if such a referential triggered action is specified in the referential integrity constraints. Insert, delete, and update Syntax: UPDATE SET =|value [FROM ] [WHERE Insert, delete, and update Example 1: UPDATE PROJECT SET PLOCATION = 'Bellaire', DNUM = 5 WHERE PNUMBER=10; Example 2: UPDATE EMPLOYEE SET SALARY = SALARY *1.1 WHERE DNO IN ( SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research‘ );