SQL Database Lecture PDF

Database systems SQL Dr. Nahla Bishri Chapter 6: Basic SQL  SQL The name SQL is presently expanded as Structured Query Language. Originally, SQL was called SEQUEL (Structured English QUEry Language) and was designed and implemented at IBM Research as the interface for...

Database systems SQL Dr. Nahla Bishri Chapter 6: Basic SQL  SQL The name SQL is presently expanded as Structured Query Language. Originally, SQL was called SEQUEL (Structured English QUEry Language) and was designed and implemented at IBM Research as the interface for an experimental relational database system called SYSTEM R. SQL is now the standard language for commercial relational DBMSs. SQL is a comprehensive database language: It has statements for data definitions, queries, and updates. Hence, it is both a DDL and a DML. In addition, it has facilities for defining views on the database, for specifying security and authorization, for defining integrity constraints, and for specifying transaction controls. It also has rules for embedding SQL statements into a general-purpose programming language such as Java or C/C+ SQL Data Definition and Data Types SQL uses the terms table, row, and column for the formal relational model terms relation, tuple, and attribute, respectively. The main SQL command for data definition is the CREATE statement, which can be used to create schemas, tables (relations), types, and domains, as well as other constructs such as views, assertions, and triggers. Schema and Catalog Concepts in SQL An SQL schema is identified by a schema name and includes an authorization identifier to indicate the user or account who owns the schema, as well as descriptors for each element in the schema. Schema elements include tables, types, constraints, views, domains. A DBMS can manage multiple databases DBA (or authorized users) can use CREATE SCHEMA to have distinct databases For example, the following statement creates a schema called COMPANY owned by the user with authorization identifier ‘Jsmith’. CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith; Each database has a schema name (e.g. COMPANY). Table names can be prefixed by schema name if multiple schemas exist (e.g. COMPANY.EMPLOYEE) The privilege to create schemas, tables, and other constructs must be explicitly granted to the relevant user accounts by the system administrator or DBA. SQL Cont. These SQL commands are mainly categorized into five categories as: 1.DDL – Data Definition Language 2.DQL – Data Query Language 3.DML – Data Manipulation Language 4.DCL – Data Control Language 5. TCL – Transaction Control Language DDL commands Data Definition Language consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. DDL is a set of SQL commands used to create, modify, and delete database structures but not data. CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers). DROP: This command is used to delete objects from the database. ALTER: This is used to alter the structure of the database. TRUNCATE: This is used to remove all records from a table, including all spaces allocated for the records are removed. COMMENT: This is used to add comments to the data dictionary. RENAME: This is used to rename an object existing in the database. DQL (Data Query Language): DQL statements are used for performing queries on the data within schema objects. The purpose of the DQL Command is to get some schema relation based on the query passed to it. List of DQL: SELECT: It is used to retrieve data from the database.  It is the component of the SQL statement that controls access to data and to the database. List of DML commands: DML(Data INSERT : It is used to insert data into a table. Manipulation UPDATE: It is used to update existing data within a table. Language): DELETE : It is used to delete records from a database table. LOCK: Table control concurrency.  DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the DCL (Data database system. Control List of DCL commands: GRANT: This command gives users access privileges Language): to the database. REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.  Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. TCL If any of the tasks fail, the transaction fails. Therefore, a transaction has only two results: (Transaction success or failure. COMMIT: Commits a Transaction. Control ROLLBACK: Rollbacks a transaction in case of any error Language): occurs. SAVEPOINT: Sets a save point within a transaction. SET TRANSACTION: Specifies characteristics for the transaction. DDL commands  The CREATE TABLE Command in SQL 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), DATE, and other data types) The SQL schema in which the relations are declared is implicitly specified in the environment in which the CREATE TABLE statements are executed. Alternatively, we can explicitly attach the schema name to the relation's name, separated by a period. For example, by writing: The CREATE TABLE Command in SQL Cont. CREATE TABLE can also specify the primary key, UNIQUE keys, and referential integrity constraints (foreign keys) Via the PRIMARY KEY, UNIQUE, and FOREIGN KEY phrases Attribute Data Types and Domains in SQL where i, the precision, is the total number of decimal digits and j, the scale, is the number of digits after the decimal point Attribute Data Types and Domains in SQL Cont. Another variable-length string data type called CHARACTER LARGE OBJECT or CLOB is also available to specify columns that have large text values, such as documents. The CLOB maximum length can be specified in kilobytes (K), megabytes (M), or gigabytes (G). For example, CLOB(20M) specifies a maximum length of 20 megabytes. Another variable-length bitstring data type called BINARY LARGE OBJECT or BLOB is also available to specify columns that have large binary values, such as images. For example, BLOB(30G) specifies a maximum length of 30 gigabits. Attribute Data Types and Domains in SQL Cont.  Date& time datatypes Cont. The < (less than) comparison can be used with dates or times An earlier date is considered to be smaller than a later date, and similarly with time. Literal values are represented by single-quoted strings preceded by the keyword DATE or TIME; for example, DATE ‘2014-09-27’ or TIME ‘09:12:47’. A timestamp data type (TIMESTAMP) includes the DATE and TIME fields, plus a minimum of six positions for decimal fractions of seconds and an optional WITH TIME ZONE qualifier. Literal values are represented by single-quoted strings preceded by the keyword TIMESTAMP, with a blank space between data and time; for example, TIMESTAMP ‘2014-09-27 09:12:47.648302’.  Specifying Attribute Constraints and Attribute Defaults Because SQL allows NULLs as attribute values, a constraint NOT NULL may be specified if NULL is not permitted for a particular attribute. It is also possible to define a default value for an attribute by appending the clause DEFAULT to an attribute definition. If no default clause is specified, the default value is NULL for attributes that do not have the NOT NULL constraint Specifying Attribute Constraints and Attribute Defaults Cont. Another type of constraint can restrict attribute or domain values using the CHECK clause following an attribute or domain definition. For example, suppose that department numbers are restricted to integer numbers between 1 and 20; then, we can change the attribute declaration of Dnumber in the DEPARTMENT table. The CHECK clause can also be used in conjunction with the CREATE DOMAIN statement.  Specifying Key and Referential Integrity Constraints The PRIMARY KEY clause specifies one or more attributes that make up the primary key of a relation. The UNIQUE clause specifies alternate (unique) keys, also known as candidate keys. Referential integrity is specified via the FOREIGN KEY clause. The default action that SQL takes for an integrity violation is to reject the update operation that will cause a violation, which is known as the RESTRICT option. The options include SET NULL, CASCADE, and SET DEFAULT. An option must be qualified with either ON DELETE or ON UPDATE. In general, the action taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE: The value of the affected referencing attributes is changed to NULL for SET NULL and to the specified default value of the referencing attribute for SET DEFAULT. Specifying Key and Referential Integrity Constraints Cont. The action for CASCADE ON DELETE is to delete all the referencing tuples, whereas the action for CASCADE ON UPDATE is to change the value of the referencing foreign key attribute(s) to the updated (new) primary key value for all the referencing tuples. SQL Alter table ALTER TABLE – ADD ADD is used to add columns into the existing table. Sometimes we may require to add additional information, in that case we do not require to create the whole database again SQL Alter table Cont. Alter table Example Alter table Example SQL Drop DROP is used to delete a whole database or just a table. To delete the whole database DROP DATABASE student_data; After running the above query whole database will be deleted. DML commands Basic Retrieval Queries in SQL The SELECT-FROM-WHERE Structure of Basic SQL Queries The SELECT-FROM-WHERE Structure The SELECT-FROM-WHERE Structure The SELECT-FROM-WHERE Structure Q11 retrieves the salary of every employee; if several employees have the same salary, that salary value will appear as many times in the result of the query, as shown in Figure 6.4(a). If we are interested only in distinct salary values, we want each value to appear only once, regardless of how many employees earn that salary. By using the keyword DISTINCT as in Q11A, we accomplish this, as shown in Figure 6.4(b). Union, except, intersection There are set union (UNION), set difference (EXCEPT), and set intersection (INTERSECT) operations. The relations resulting from these set operations are sets of tuples; that is, duplicate tuples are eliminated from the result. These set operations apply only to type compatible relations, so we must make sure that the two relations on which we apply the operation have the same attributes and that the attributes appear in the same order in both relations Union, except, intersection Cont. INSERT, DELETE, and UPDATE Statements in SQL Insert statement  Constraints specified in the DDL are automatically enforced by the DBMS when updates are applied to the database. Can insert multiple tuples in one INSERT statement – The values in each tuple are enclosed in parentheses Insert statement Cont. Delete statement Removes tuples from a relation: Includes a WHERE-clause to select the tuples to be deleted Referential integrity should be enforced (via REJECT, CASCADE, SET NULL, or SET DEFAULT) Tuples are deleted from only one table at a time (unless CASCADE is specified on a referential integrity constraint) Missing WHERE-clause deletes all tuples in the relation; the table then becomes an empty table Number of tuples deleted is the number of tuples selected by the WHERE-clause Update statement Used to modify attribute values of one or more selected tuples A WHERE-clause selects the tuples to be modified An additional SET-clause specifies the attributes to be modified and their new values Each command modifies tuples in the same relation Referential integrity should be enforced (via REJECT, CASCADE, SET NULL, or SET DEFAULT) Update statement Cont. Thanks