The document provides instructions for students on creating and manipulating databases using SQL, including how to create, alter, and drop databases and tables, add constraints to columns and tables, and perform lab tasks to practice these skills such as creating a table with various column properties and constraints then altering it by adding, changing, and deleting columns. The document covers key SQL data definition language commands and constraints that enforce rules on data inserted, deleted or updated in tables.
The document provides instructions for students on creating and manipulating databases using SQL, including how to create, alter, and drop databases and tables, add constraints to columns and tables, and perform lab tasks to practice these skills such as creating a table with various column properties and constraints then altering it by adding, changing, and deleting columns. The document covers key SQL data definition language commands and constraints that enforce rules on data inserted, deleted or updated in tables.
The document provides instructions for students on creating and manipulating databases using SQL, including how to create, alter, and drop databases and tables, add constraints to columns and tables, and perform lab tasks to practice these skills such as creating a table with various column properties and constraints then altering it by adding, changing, and deleting columns. The document covers key SQL data definition language commands and constraints that enforce rules on data inserted, deleted or updated in tables.
The document provides instructions for students on creating and manipulating databases using SQL, including how to create, alter, and drop databases and tables, add constraints to columns and tables, and perform lab tasks to practice these skills such as creating a table with various column properties and constraints then altering it by adding, changing, and deleting columns. The document covers key SQL data definition language commands and constraints that enforce rules on data inserted, deleted or updated in tables.
Department of Computer Science & Information Technology
Khwaja Fareed University of Engineering & Information Technology
Course: Database Systems
Lab Instructor: Ms. Hira Kanwal
Student Name
Student Roll #
Department
Batch/Year/Section
For Lab. Instructor
Marks Signature
KFUEIT Department of CS/IT
xvi Lab Manual # 3 Creating & Manipulating Databases
3.1. Objective After this lab, you would be able to understand: 1. Data Definition Language 2. Creating Database using query 3. Create, Alter, Drop table using Query 4. Sql Constraints on Columns & Tables
3.2. Create/ Use /DROP/ Show Databases
1. CREATE Database
CREATE DATABASE NEW;
2. USE Existing Database
Use NEW;
3. Delete Database
DROP DATABASE NEW;
** where NEW is the name of database.
4. Showing list of available Databases
select * from sys.databases
where name NOT In ('master','tempdb','model','msdb');
3.3. SQL CREATE /DROP/ALTER Table
3.3.1. CREATE Table
To create a table named users_info, by using query:
CREATE TABLE users_info(
rollno INT, name VARCHAR(30), address VARCHAR(70), contact_no VARCHAR(12) );
To add a new column to an existing table, we can use the following query:
Alter table tablename add column_name int;
You cannot define where a new column is located, each newly inserted column is always located last. If the table already contains columns with values, the new column will be populated with NULL values for all these rows.
To change a column’s datatype of an existing table, we use the following query:
ALTER TABLE table_name
ALTER COLUMN column_name column_datatype
Example: ALTER TABLE detail ALTER COLUMN DOB DateTime;
To drop a column from an existing table, we can use the following query:
ALTER TABLE table_name DROP COLUMN column_name;
3.4. SQL Constraints
SQL constraints (Rules) are used to specify rules for the data in a table. Constraints enforce rules on the data in a table whenever a row is inserted, deleted, or updated. Constraints can be defined at the column or table level.
If there is any violation between the constraint and the data action, the action is aborted by the constraint.
In SQL, we have the following constraints:
i. NOT NULL - Indicates that a column cannot store NULL value
ii. UNIQUE - Ensures that each row for a column must have a unique value iii. PRIMARY KEY - A combination of a NOT NULL and UNIQUE. iv. FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table v. CHECK - Ensures that the value in a column meets a specific condition vi. DEFAULT - Specifies a default value for a column
emp_name varchar(25) CONSTRAINT emps_emnm_nn NOT NULL);
Another Syntax for applying the same NOT NULL constraint is:
CREATE TABLE emps
(emp_id decimal(3) PRIMARY KEY,
emp_name varchar(25) NOT NULL);
NOTE:
1. NOT NULL constraint can only be defined at the column level.
2. Keyword CONSTRAINT and constraint name are optional parameters, but are recommended to use.
3.5.2. UNIQUE Constraint
In SQL Server, the Unique constraint requires that every value in a column (or set of columns) be unique. The syntax for defining a UNIQUE Constraint is as follows:
In SQL Server, the Check constraint defines a condition that each row must satisfy.
The condition written in the CHECK is quite similar in its structure to each of the conditions written in a WHERE statement.
The condition in the CHECK part must not include:
1. Functions such as GETDATE()
2. Subqueries
Syntax:
CREATE TABLE stinfo(
no INT PRIMARY KEY, name VARCHAR(30) NOT NULL, std VARCHAR(18) DEFAULT 'M.Sc.(CS)', fees_pay INT CHECK(fees_pay BETWEEN 1000 AND 3000) );
Examples:
1. [Roll#] Varchar(12) CHECK([Roll#] like 'CS14%')
2. City varchar(20) CHECK (city IN ('RYK','LHR','ISB')) 3. Scholarship int CHECK (scholarship BETWEEN 5000 AND 20000)
3.5.4. DEFAULT Constraint
If inserting data does not provide any specific value, the default constraint automatically assign default value, only if you specified DEFAULT constraint.
Example:
CREATE TABLE stu_info(
no INT PRIMARY KEY, name VARCHAR(30) NOT NULL, std VARCHAR(18) DEFAULT 'M.Sc.(CS)', fees_pay INT DEFAULT 2000);
In SQL Server, the Foreign Key constraint designates a column as a Foreign Key and establishes a relationship between a Primary Key in different table. Foreign Key is used to link two tables. The syntax for defining a Foreign key Constraint is as follows:
To create a UNIQUE constraint on a column when the table is already created, use the following sql: Alter Table Emp Add Constraint emp_phone_unq UNIQUE(Phone_No);
To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL: ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID);
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL: ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL: ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18);
To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:
Create a table “EmployeeTable” in database with following structure:
i. EmpNo NOT NULL number, Primary Key ii. EmpName VarChar(40) NOT NULL iii. Job NOT NULL Char(10) iv. DeptNo NOT NULL number v. PHONE_NO number vi. YearOfEnrollment year vii. Percentage float (two number, two decimal points)
3.7.1. Alter Table
After creating the above table, Add two more columns in “EmployeeTable”
2. Add Constraint UNIQUE (DName) 3. Add Constraint Foreign Key (DeptNo of Emp table) 4. Add column Address in Emp table. It should not be null and should be unique.
