SQL Server Sheet 3
7- Alter Table Statement
عشان اغير في الهيكل او الجدول مش تعديل علي البيانات – يعني مثال بعد ما عملت جدولalter بستخدم
data type اغير حجمه او الcolumn نسيته او اعدل في مواصفات الcolumn لقيت اني محتاجة ازود
زيادة و عايزةcolumn او اصال زودتrename او اشيلها او اعمل السمهnull او ازود انه يكون
خالص مش محتجاهconstraint علي جدول معين او امسحconstraint امسحه – او كمان عايزة ازود
ADD column
ALTER TABLE table_name
ADD column_name data_type column_constraint;
**ممكن نزود واحد او اكتر
ALTER TABLE table_name
ADD column_name1 data_type1 column_constraint1;
ADD column_name2 data_type2 column_constraint2;
ADD column_name3 data_type3 column_constraint3;
MODIFY column
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type(new_size);
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type(new_size) NULL;
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type(new_size) NOT
NULL;
او العكس الزم يكون الجدول فاضي من غير بياناتINT لVARCHAR منdata type ** لو محتاج تغير نوع ال
DROP column
ALTER TABLE table_name
DROP COLUMN column_name;
DROP TABLE table_name; ** انما عشان تمسح الجدول كله
Prepared by: Asmaa Zakria
SQL Server Sheet 3
ADD constraint
UNIQUE
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);
PRIMARY key
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY
(column_name);
FOREIGN key
ALTER TABLE child_table_name foreign key
ADD CONSTRAINT constraint_name PRIMARY KEY
(column_name)
REFERENCES parent_table_name (parent_column_name);
DROP constraint
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
RENAM table
EXEC sp_rename 'old_table_name', 'new_table_name';
جاهزة عشانfunction ديsp_rename اسمهاsql server في الbuild in جاهزةstored procedures ** في
execute تتنفذ بنكتب قبلهاstored procedures اي
RENAM column
EXEC sp_rename 'table_name.column_name',
'new_column_name', 'object_type_name';
'COLUMN' ف هزود في االخرcolumn اللي عايزة اغير اسمه هنا انا بغير اسم الobject ** بحدد نوع ال
Prepared by: Asmaa Zakria
SQL Server Sheet 3
REMEMBER again :
- Structured Query Language (SQL) is the database language that is used to
perform certain operations on the existing database and also we can use this
language to create a database.
- SQL commands are mainly categorized into four categories as:
o DDL – Data Definition Language
o DML – Data Manipulation Language
o DQL – Data Query Language
o DCL – Data Control Language
- DDL or Data Definition Language actually 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. These commands are normally not used
by a general user, who should be accessing the database via an
application.
List of DDL commands:
- 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.
- CREATE INDEX: this is used to create indexes in tables. Indexes are used
to retrieve data from the database more quickly than otherwise. The users
cannot see the indexes; they are just used to speed up searches/queries.
1- Here is the schema of a company database, please implement it on any RDBMS
you like (only structure do not insert data)
Prepared by: Asmaa Zakria
SQL Server Sheet 3
Prepared by: Asmaa Zakria
SQL Server Sheet 3
Prepared by: Asmaa Zakria
SQL Server Sheet 3
2- Here is the schema of a Bike Stores, please implement it on any RDBMS you like.
Solution:
-- create schemas
CREATE SCHEMA production;
go
CREATE SCHEMA sales;
go
-- create tables
CREATE TABLE production.categories (
category_id INT IDENTITY (1, 1) PRIMARY KEY,
category_name VARCHAR (255) NOT NULL
);
CREATE TABLE production.brands (
brand_id INT IDENTITY (1, 1) PRIMARY KEY,
brand_name VARCHAR (255) NOT NULL
);
Prepared by: Asmaa Zakria
SQL Server Sheet 3
CREATE TABLE production.products (
product_id INT IDENTITY (1, 1) PRIMARY KEY,
product_name VARCHAR (255) NOT NULL,
brand_id INT NOT NULL,
category_id INT NOT NULL,
model_year SMALLINT NOT NULL,
list_price DECIMAL (10, 2) NOT NULL,
FOREIGN KEY (category_id) REFERENCES production.categories (category_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (brand_id) REFERENCES production.brands (brand_id) ON DELETE
CASCADE ON UPDATE CASCADE
);
CREATE TABLE sales.customers (
customer_id INT IDENTITY (1, 1) PRIMARY KEY,
first_name VARCHAR (255) NOT NULL,
last_name VARCHAR (255) NOT NULL,
phone VARCHAR (25),
email VARCHAR (255) NOT NULL,
street VARCHAR (255),
city VARCHAR (50),
state VARCHAR (25),
zip_code VARCHAR (5)
);
CREATE TABLE sales.stores (
store_id INT IDENTITY (1, 1) PRIMARY KEY,
store_name VARCHAR (255) NOT NULL,
phone VARCHAR (25),
email VARCHAR (255),
street VARCHAR (255),
city VARCHAR (255),
state VARCHAR (10),
zip_code VARCHAR (5)
);
CREATE TABLE sales.staffs (
staff_id INT IDENTITY (1, 1) PRIMARY KEY,
first_name VARCHAR (50) NOT NULL,
last_name VARCHAR (50) NOT NULL,
email VARCHAR (255) NOT NULL UNIQUE,
phone VARCHAR (25),
active tinyint NOT NULL,
store_id INT NOT NULL,
manager_id INT,
FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE
CASCADE ON UPDATE CASCADE,
Prepared by: Asmaa Zakria
SQL Server Sheet 3
FOREIGN KEY (manager_id) REFERENCES sales.staffs (staff_id) ON DELETE NO
ACTION ON UPDATE NO ACTION
);
CREATE TABLE sales.orders (
order_id INT IDENTITY (1, 1) PRIMARY KEY,
customer_id INT,
order_status tinyint NOT NULL,
-- Order status: 1 = Pending; 2 = Processing; 3 = Rejected; 4 = Completed
order_date DATE NOT NULL,
required_date DATE NOT NULL,
shipped_date DATE,
store_id INT NOT NULL,
staff_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES sales.customers (customer_id) ON
DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE
CASCADE ON UPDATE CASCADE,
FOREIGN KEY (staff_id) REFERENCES sales.staffs (staff_id) ON DELETE NO
ACTION ON UPDATE NO ACTION
);
CREATE TABLE sales.order_items (
order_id INT,
item_id INT,
product_id INT NOT NULL,
quantity INT NOT NULL,
list_price DECIMAL (10, 2) NOT NULL,
discount DECIMAL (4, 2) NOT NULL DEFAULT 0,
PRIMARY KEY (order_id, item_id),
FOREIGN KEY (order_id) REFERENCES sales.orders (order_id) ON DELETE
CASCADE ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES production.products (product_id) ON
DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE production.stocks (
store_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (store_id, product_id),
FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ON DELETE
CASCADE ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES production.products (product_id) ON
DELETE CASCADE ON UPDATE CASCADE
);
Prepared by: Asmaa Zakria
