SQL and DDL PDF

Data Structures Introduction to SQL SESSION 4 WHAT IS SQL ßelal A. Hamed What is SQL? SQL stands for Structured Query Language. SQL is a standard language for querying and manipulating data. SQL is a standard language used to create, maintain and control database....

Data Structures Introduction to SQL SESSION 4 WHAT IS SQL ßelal A. Hamed What is SQL? SQL stands for Structured Query Language. SQL is a standard language for querying and manipulating data. SQL is a standard language used to create, maintain and control database. ßelal A. Hamed SQL Commands SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data. SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users. SQL (STRUCTURED QUERY LANGUAGE): Is used to perform operations on the records stored in the database, such as updating records, inserting records, deleting records, creating and modifying database tables, views, etc. SQL is not a database system, but it is a query language. ßelal A. Hamed Table Components ßelal A. Hamed Types of SQL Commands SQL DDL DML DCL TCL Data Definition Language Data manipulation Data Control Language language Transaction Control CREATE GRANT COMMIT SELECT ALTER REVOKE ROLLBACK INSERT DROP SAVEPOINT UPDATE RENAME DELETE MERGE TRUNCATE COMMENT ßelal A. Hamed Database design SESSION 4 INTRODUCTION TO SQL DDL Data Definition Language DDL is a type of SQL command that is used to define and modify the structure of database objects such as tables, indexes, and views. DDL commands are used by database administrators and developers to manage and maintain the database structure. These commands are typically used in conjunction with DML (Data Manipulation Language) commands, which are used to manipulate data stored in the database. ßelal A. Hamed Data Definition Language Some of the commonly used DDL commands are: 1. CREATE: Used to create new database objects, such as tables, indexes, views, and stored procedures. 2. ALTER: Used to modify the structure of existing database objects, such as adding, deleting, or modifying columns in a table. 3. DROP: Used to delete an entire database object or some parts of it, such as deleting a table or a column in a table. 4. TRUNCATE: Used to remove all the data from a table while keeping its structure. 5. RENAME: Used to rename an existing database object, such as a table, column, or index. 6. COMMENT: Used to add comments to the database objects, such as tables or columns, to document their purpose or usage. ßelal A. Hamed Database design SESSION 4 INTRODUCTION TO SQL DDL CREATE (DATABASE + TABLES) Create Database The CREATE DATABASE statement is used to create a new SQL database. Syntax: CREATE DATABASE databasename ; CREATE DATABASE [database name]; Examples: CREATE DATABASE studentDB; CREATE DATABASE [stores DB]; ßelal A. Hamed Create Table CREATE It is used to create a new table in the database Syntax: Example: ßelal A. Hamed SQL Data Type A data type determines the type of data that can be stored in a database column. The most commonly used data types are: ✓ Alphanumeric: data types used to store characters, numbers, special characters, or nearly any combination. ✓ Numeric. ✓ Date and Time. ßelal A. Hamed  Bit int SQL Data Type Exact Numeric numeric money Float Approximate numerics Real Date Date and time Time S Q L D ATA T Y P E Datetime char Character strings varchar Text Nchar Unicode character strings Nvarchar Ntext Binary Binary strings Image Xml Other data types Table ßelal A. Hamed SQL Constraints SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and Table level constraints apply to the whole table. ßelal A. Hamed SQL Constraints The following constraints are commonly used in SQL: 1. Not Null Ensures that a column cannot have a NULL value 2. Check Ensures that the values in a column satisfies a specific condition 3. Default Sets a default value for a column if no value is specified 4. Unique Ensures that all values in a column are different 5. Primary Key is a combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table 6. Referential Integrity ( FK ) Prevents actions that would destroy links between tables. 7. Auto-increment The IDENTITY property will auto-increment the column up from number 1. (Note that the data type of the column has to be an integer.) ßelal A. Hamed SQL Constraints ßelal A. Hamed Database design SESSION 4 INTRODUCTION TO SQL DDL EXAMPLE Create Tables Examples CREATE TABLE customer CREATE TABLE customer ( ID int Not Null, ( ID int Primary key identity(1,1), First_Name char(50), First_Name char(50), Last_Name char(50), Last_Name char(50), City char(25), City char(25), Birth_Date date, Birth_Date date Primary key (ID) ); ); ßelal A. Hamed Example: Students Sheet Students Sheet Platform Name : SWE Platform Description: Software Engineering Graduate Profile: ALL Found. Total Att. Appno Name F-code Faculty Major Address Telno Startdate Grade Hrs 123 Ahmed SC-phy Science Physics Haram 3386842 A 600 14 Sep 0 124 Mona Eng-cs Engineering Computer Dokki 3389745 B 591 15 Sep 5,33897 445 127 Ali Com-ac Commerce Accounting Nasr City 2241593 A 550 21 Sep 9 223 Karim Med-bio Medicine Biochemistry Sheraton 2286845 C 600 14 Sep 6 ßelal A. Hamed Example: Students Sheet Platform (name , desc , graduate) Students(appno, name , f-code, address) Std_Tel(appno, telno) Students_platform(pfname,appno,Foundgrade,attd, start_date) Fac_majors(f-code ,faculty , major) ßelal A. Hamed Example: Students Sheet Independent tables ßelal A. Hamed Example: Students Sheet FK-dependent tables ßelal A. Hamed Example: Students Sheet FK-dependent tables ßelal A. Hamed Example: Students Sheet Database Diagram ßelal A. Hamed Database design SESSION 4 INTRODUCTION TO SQL DDL DROP (DATABASE + TABLES) Drop command Drop: Removes one or more table definitions and all data It is used to delete both the structure and record stored in the table. Syntax: DROP DATABASE databasename DROP TABLE Start date name ßelal A. Hamed Drop command EXAMPLES DROP DATABASE testDB; DROP TABLE student; ßelal A. Hamed Database design SESSION 4 INTRODUCTION TO SQL DDL ALTER (COLUMN + CONSTRAINT) Alter command Alter: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. ßelal A. Hamed Alter command ßelal A. Hamed Alter command Syntax: Add: ALTER TABLE table_name ADD column_name datatype ; Remove: ALTER TABLE table_name DROP COLUMN column_name; Modify: ALTER TABLE table_name ALTER COLUMN column_name datatype; ßelal A. Hamed Alter command Examples EXAMPLE 1 EXAMPLE 2 ALTER TABLE Customers ALTER TABLE Customers ADD Email varchar(255); DROP COLUMN Email; ßelal A. Hamed Database design SESSION 4 INTRODUCTION TO SQL DDL TRUNCATE Truncate command Truncate: Itis used to delete all the rows from the table and free the space containing the table. Syntax: Example ßelal A. Hamed Database design SESSION 4 INTRODUCTION TO SQL DDL EXAMPLES Data Definition Language Summary CREATE DATABASE dbName ; DROP DATABASE databaseName DROP TABLE table_name CREATE TABLE table_name ALTER TABLE table_name (col_Name datatype constraints, ADD column_name datatype ; ALTER TABLE table_name col_Name2 datatype constraints, DROP COLUMN column_name;... ALTER TABLE table_name ALTER ); COLUMN column_name datatype; ßelal A. Hamed Assignment 1 ßelal A. Hamed Assignment 2: Hospital Database ßelal A. Hamed Assignment 3: Airport Database ßelal A. Hamed Assignment 4: Faculty Database Student(St_id, st_fname, st_Lname, st_age, st_super, Dept_ID) Course(Crs_id, Crs_Name, Crs_Duration, Top_id) Topic(Top_ID, Top_Name) Stud_Course(St_ID,Crs_ID, grade) Instructor(Ins_ID, ins_Name, Address, Salary,Dept_ID) Ins_Course(Ins_ID,Crs_ID, Evalution) Department(Dept_ID, Dept_Name, Manager_ID, HireDate) ßelal A. Hamed