Introduction To SQL (Part 1) PDF

IT1924 Introduction to SQL (Part I) SQL Data Definition Commands...

IT1924 Introduction to SQL (Part I) SQL Data Definition Commands CREATE DATABASE – creates a new database Fundamentals o Syntax: CREATE DATABASE database_name; A query is a specific request for data manipulation issued by the o Example: CREATE DATABASE myDB; end-user or the application to the DBMS. DROP DATABASE – deletes an existing database SQL: o Syntax: DROP DATABASE database_name; o Stands for Structured Query Language o Example: DROP DATABASE myDB; o Pronounced as S-Q-L or “sequel” CREATE TABLE – creates a new table in a database o Consists of commands that: o Syntax: CREATE TABLE table_name (column1 datatype, …); ▪ Create database and table structures o Example: CREATE TABLE Students (StudentID ▪ Perform various types of data manipulation and varchar(11), LastName varchar(99), FirstName data administration varchar(99), Section varchar(5)); ▪ Query the database to extract useful information DROP TABLE – deletes an existing table in a database Popular Database Management Tools o Syntax: DROP TABLE table_name; o Microsoft SQL Server o Example: DROP TABLE Students; o MySQL o To delete only the table's data: o Oracle RDBMS ▪ Syntax: TRUNCATE TABLE table_name; o Microsoft Access ▪ Example: TRUNCATE TABLE Students; SQL Data Types: ALTER TABLE – Adds, deletes, or modifies columns in an existing Category Common Data Types table Exact numeric bigint, bit, decimal, int, money, o Syntax to add: ALTER TABLE table_name ADD column numeric datatype; Approximate numeric float, real o Example: ALTER TABLE Students ADD MiddleName Date and time date, datetime, time varchar(99); Character strings char, text, varchar o Syntax to delete: ALTER TABLE table_name Unicode character nchar, ntext, nvarchar DROP COLUMN column; strings o Example: ALTER TABLE Students DROP COLUMN Binary strings binary, image, varbinary Section; Other data types cursor, sql_variant, table, o Syntax to modify: ALTER TABLE table_name uniqueidentifier, xml ALTER COLUMN column datatype; o Example: ALTER TABLE Students ALTER COLUMN SQL Operators: MiddleName nvarchar(99); Category Operators Arithmetic +, -, *, /, % SQL Constraints Comparison =, >, =, =15)); o Example: CREATE TABLE Students (StudentID CHECK on ALTER TABLE – ensures that all values in a column of varchar(11) NOT NULL UNIQUE, LastName varchar(99) an existing table satisfy a specific condition NOT NULL, FirstName varchar(99) NOT NULL, Section o Syntax: ALTER TABLE table_name ADD CHECK varchar(5)); (condition); UNIQUE on ALTER TABLE – creates a UNIQUE constraint on a o Example: ALTER TABLE Students ADD CHECK column of an existing table (Age>=15); o Syntax: ALTER TABLE table_name ADD UNIQUE DEFAULT on CREATE TABLE – sets a default value for a column (column); when there is no value specified o Example: ALTER TABLE Students ADD UNIQUE o Example: CREATE TABLE Students (StudentID (StudentID); varchar(11) NOT NULL, LastName varchar(99) NOT PRIMARY KEY on CREATE TABLE – uniquely identifies each row NULL, FirstName varchar(99) NOT NULL, Section in a table varchar(5) DEFAULT 'Not yet enrolled'); o Example: CREATE TABLE Students (StudentID DEFAULT on ALTER TABLE – sets a default value for a column of varchar(11) NOT NULL PRIMARY KEY, LastName an existing table when there is no value specified varchar(99) NOT NULL, FirstName varchar(99) NOT o Syntax: ALTER TABLE table_name ADD CONSTRAINT NULL, Section varchar(5)); constraint_name DEFAULT 'value' FOR column; PRIMARY KEY on ALTER TABLE – creates a PRIMARY KEY o Example: ALTER TABLE Students ADD CONSTRAINT constraint on a column of an existing table df_section DEFAULT 'Not yet enrolled' FOR Section; o Syntax: ALTER TABLE table_name ADD PRIMARY KEY (column); o Example: ALTER TABLE Students ADD PRIMARY KEY (StudentID); FOREIGN KEY on CREATE TABLE – uniquely identifies a row in another table o Example: CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY, TableNumber int NOT NULL, CustomerID int FOREIGN KEY REFERENCES Customers (CustomerID)); FOREIGN KEY on ALTER TABLE – creates a FOREIGN KEY constraint on a column of an existing table References: o Syntax: ALTER TABLE table1_name ADD FOREIGN Coronel, C. and Morris, S. (2017). Database systems: design, implementation, and KEY (table1_column) REFERENCES table2_name management (12th ed.). USA: Cengage Learning. (table2_column); Elmasri, R. and Navathe, S. (2016). Fundamentals of database systems (7th ed.). USA: Pearson Higher Education. o Example: ALTER TABLE Orders ADD FOREIGN KEY Kroenke, D. and Auer, D. (2016). Database processing: fundamentals, design, and (CustomerID) REFERENCES Customers (CustomerID); implementation. England: Pearson Education Limited. 06 Handout 1 *Property of STI  [email protected] Page 2 of 2