1 / 58

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

quilla
Download Presentation

SQL: Schema Definition, Basic Constraints, and Queries

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. Chapter 8 SQL: Schema Definition, Basic Constraints, and Queries

  2. Create database • Syntax: • Example: • CREATE DATABASE QuanlySach CREATE DATABASE database_name

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

  4. Create database CREATE DATABASELogical_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 )

  5. 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% )

  6. 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, ... )

  7. Create table • Example: CREATE TABLE Nhanvien (MaNhanvien char(5), HoNhanvienVarchar(30), TenNhanvienVarchar(25), NgaysinhSmalldatetime )

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

  9. Attribute Data Types and Domains in SQL • Character-string: • CHAR(n) or CHARACTER(n). • nis the number of characters-or varying length-VARCHAR(n) or CHAR • VARYING(n) or CHARACTER VARYING(n) • nis 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.

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

  11. Data integrity(toànvẹndữliệu) • Data integrity: ensures the quality of the data in the database. • For example, if an employee is entered with an employeeIDvalue 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)

  12. 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).

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

  14. Declarative integrity • Default constraint: • Define a Default constraint when creating table • Define a Default constraint when existing table: CREATE TABLE Table_name (Column_name Datatype [NULL| NOT NULL] [CONSTRAINT Constraint_name] DEFAULT expression[…]) ALTER TABLE Table_name ADD [CONSTRAINT Constraint_name] DEFAULT expression FOR column_name

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

  16. Declarative integrity • Delete Default constraint: • Example: ALTER TABLE Orders DROP CONSTRAINT DF_Orders_OrderDate ALTER TABLE Table_name DROP CONSTRAINT Constraintname

  17. 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 )

  18. 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])} )

  19. Declarative integrity • Example: • CREATE TABLE Hoadon ( MaHD int NOT NULL CONSTRAINT PK_HoaDonPRIMARY KEY ); • CREATE TABLE Ketqua ( masvchar(10) not null, mamhvarchar(40) not null, Diem float not null, Primary key (masv, mamh) );

  20. 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 ADDColumn_name datatype [CONSTRAINT constrain_name] PRIMARY KEY ALTER TABLEtable_name ADD[CONSTRAINT constraint_name ] PRIMARY KEY {(column[ASC|DESC][,…columnN])}

  21. Declarative integrity • Remove a Primary Key Constraint: ALTER TABLE table_name DROP CONSTRAINT Constraint_name

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

  23. 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 )

  24. 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])} )

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

  26. 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_namedatatype [,...], [CONSTRAINT constraint_name ] FOREIGN KEY [ ( column [ ,...n ] ) ] REFERENCES ref_table[ ( ref_column[ ,...n ])] • )

  27. Declarative integrity Create table Phongban (Mapbint, Tenpbvarchar(30), Constraint pb_PK primary key (mapb) ) Create table nhanvien (manvint, Hotenvarchar(40), Mapbint, Constraint manv_PK primary key (manv), Contraintmapb_Fk foreign key(mapb) references phongban(mapb) )

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

  29. Declarative integrity • Example: CREATE TABLE nhanvien ( manvsmallint PRIMARY KEY CLUSTERED, tennvvarchar(50) NOT NULL , tuoimintinyint NOT NULL CHECK (tuoimin >= 18), tuoimaxtinyint NOT NULL CHECK (tuoimax <= 40) )

  30. Declarative integrity • Syntax: Create a Check Constraint Using the ALTER TABLE Statement ALTER TABLE table_name ADD [CONSTRAINT constraint_name] CHECK (logical expression)

  31. Referential integrity options • RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential integrity constraints (foreign keys) • Syntax: CREATE TABLE table_name ( colum_namedatatype [,...], [CONSTRAINT constraint_name ] FOREIGN KEY [ ( column [ ,...n ] ) ] REFERENCES ref_table[ ( ref_column[ ,...n ])] • ON DELETE SET DEFAULT ON UPDATE CASCADE • )

  32. Referential integrity options Example: Create table nhanvien (manvint, Hotenvarchar(40), Mapbint, Constraint manv_PK primary key (manv), Contraintmapb_Fk foreign key(mapb) references phongban(mapb) on delete set default on update cascade );

  33. Basic queries in SQL • TheSELECT statement: SELECT [DISTINCT] select_list[INTOnew_table ] FROMtable_source[WHEREsearch_condition] [GROUPBYgroup_by_expression] [HAVINGsearch_condition] [ORDERBYorder_expression[ASC|DESC] ]

  34. Basic queries in SQL • [DISTINCT]: remove all duplicate rows from query results. • Example: SELECTDISTINCTCust.CustomerID, CompanyName FROM Customers AS Cust INNER JOIN Orders AS Ord ONCust.CustomerID = Ord.CustomerID ORDER BY Cust.CustomerID

  35. 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 WHEREDNAME='Research‘ AND DNUMBER=DNO

  36. 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. SELECTDNa, COUNT (*), AVG (SALARY) FROMEMPLOYEE GROUP BY DNa;

  37. Basic Queries in SQL

  38. 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 GROUPBY PNUMBER, PNAME HAVINGCOUNT (*) > 2;

  39. Basic Queries in SQL

  40. Basic Queries in SQL • [ORDERBY]: to sort your SQL result sets by some column(s). • Example: • SELECTc.CustomerID, COUNT(o.orderID) Total Orders’, SUM (od.UnitPrice* od.Quantity) ‘Total Sales’ • FROM Customers c, Orders o, [Order Details] od • WHEREc.CustomerID = o.CustomerID • ANDo.OrderID = od.OrderID • GROUPBYc.CustomerID

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

  42. 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 ASE, EMPLOYEE ASS WHERE E.SUPERSSN=S.SSN

  43. Basic Queries in SQL • SQL JOIN: • INNER JOIN: return rows when there is at least one match in both tables. • Syntax: SELECTcol_name(s)FROM table1INNER JOIN table2ONtable1.col_name=table2.col_name

  44. Basic Queries in SQL • Example: SELECTCustomers.CustomerID, CompanyName, OrderID, OrderDate FROM Customers INNER JOIN Orders ONCustomers.CustomerID = Orders.CustomerID

  45. 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 righttable even no matching record found in the left table, plus matching records in the table A.

  46. Basic Queries in SQL • Syntax: SELECTcolname(s)FROMFROM table1 {LEFT OUTER JOIN | LEFT OUTER JOIN} table2 ONtable1.colname=table2.colname

  47. 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. SELECTcolumn_list • FROM table1 CROSS JOIN table2

  48. Basic Queries in SQL • Example: SELECT makh, tenkh, honv+' '+tennv AS TenNV FROM Khachhang CROSS JOIN nhanvien

More Related