Constraint

NOT NULL store_name VARCHAR (255) NOT NULL

UNIQUE - store_email VARCHAR (255) UNIQUE,
- CONSTRAINT store_uq UNIQUE (store_email),
PRIMARY KEY - store_id INT IDENTITY (1, 1) PRIMARY KEY,
- constraint store_pk PRIMARY KEY ( store_id),
FOREIGN KEY - store_id int REFERENCES store (store_id),
- FOREIGN KEY (store_id) REFERENCES store (store_id),
- CONSTRAINT fk_group FOREIGN KEY (store_id) REFERENCES
store(store_id),
CHECK - unit_price DEC(10,2) CHECK (unit_price > 0 AND
unit_price < 1200),
- unit_price DEC(10,2) CHECK (unit_price between 0 and
1200)
- CONSTRAINT unit_price_chk CHECK (unit_price between 0
and 1200),

Alter Table Statment

Columns ADD:
ALTER TABLE store
ADD store_address VARCHAR (255) NULL;
ALTER:
ALTER TABLE store
ALTER COLUMN store_address VARCHAR (100) ;
RENAM:
- EXEC sp_rename 'old_table_name', 'new_table_name'
- EXEC sp_rename 'store. store_address', 'S_address', 'COLUMN'
DROP:
ALTER TABLE store
DROP COLUMN store_address ;
Constraints ADD:
ALTER TABLE store
- ADD CONSTRAINT store_address_uq UNIQUE (store_address);
- ADD CONSTRAINT store_id _pk PRIMARY KEY (store_id);
- ADD FOREIGN KEY (store_id) REFERENCES store (store_id);
DROP:
ALTER TABLE store
DROP CONSTRAINT store_address_uq;
 INSERT , UPDATE, DELETE
INSERT -INSERT INTO store (first_name, last_name, phone)
VALUES ('Khaled','Mousa','012222586'),
('Anas','Ghareeb','010252586');
SELECT*from store;
- INSERT INTO store (first_name, last_name, phone)
OUTPUT inseted.first_name, inserted.last_name, [Link]
VALUES ('Khaled','Mousa','012222586'),
('Anas','Ghareeb','010252586'),
UPDATE UPDATE store
set first_name ='Abdallah', last_name ='Omar', phone ='012003422'
Where store_id = 2
DELETE DELETE from store
- WHERE store_id = 2;
- WHERE store_id between 2 and 4;

SELECT Statment
Basic SELECT SELECT first_name+' '+last_name as S_name, phone
FROM store;
SELECT - WHERE SELECT*FROM store ---or-- SELECT first_name, last_name, phone FROM
store
where first_name = Khaled;
SELECT - WHERE Isnull:
-In SELECT*FROM store
-Between where first_name is null; -- X is not null
-Isnull In:
SELECT*FROM store
where first_name in ('Khaled','Anas','Abdallah'); -- X not in
Between:
SELECT*FROM store
where store_id between 1 and 3; --X not between
SELECT- Distinct SELECT Distinct first_name From store;
Values SELECT Distinct first_name, last_name From store;

SELECT – SELECT*FROM store

WHERE Where first_name like 'A%'; --X not like
'%D' finish //'%na%' include//'A__' 3 letters and start with A
Liked Operator '[AK]%' start A or K //'[A-C]%' start in range

Sorting Results-Order by Clause

SELECT first_name,last_name
From store ORDER BY first_name DESC; --X ASC
 Join Tables
Inner Join SELECT first_name, last_name, email, order_id, oreder_date, store_id
from cusomers c JOIN oreders o = inner join (JOIN replace to , )
ON c.custumer_id = o. customer_id; (ON replace to WHERE)
Outer Join LEFT OUTER JOIN:
SELECT first_name, last_name, email, order_id, oreder_date, store_id
from cusomers c LEFT OUTER JOIN oreders o ‫سيظهر كل العمالء من اليسار بدون اسثناء‬
ON c.custumer_id = o. customer_id;
RIGHT OUTER JOIN:
SELECT first_name, last_name, email, order_id, oreder_date, store_id
from cusomers c RIGHT OUTER JOIN oreders o ‫سيظهر كل االوردرمن اليمين بدون اسثناء‬
ON c.custumer_id = o. customer_id;
FULL OUTER JOIN:
SELECT first_name, last_name, email, order_id, oreder_date, store_id
from cusomers c FULL OUTER JOIN oreders o ‫سيظهر كل االوردر و العمالء بدون اسثناء‬
ON c.custumer_id = o. customer_id;
Join more than SELECT
2 Tables first_name,last_name,order_id,oreder_date,store_name,[Link],[Link]
FROM customers c JOIN orders o on c.customer_id = o.customer_id
JOIN stores s on o.store_id = S.store_id;
Another Solution :
SELECT
first_name,last_name,order_id,oreder_date,store_name,[Link],[Link]
FROM customers c, orders o, stores s
WHERE c.customer_id = o.customer_id and o.store_id = S.store_id;

Aggregate Function
MAX-MIN-AVG- SELECT MAX (list_price)"Highest Price", MIN(list_price)"Lowest Price",
SUM-COUNT AVG(list_price) as "Average Price", SUM(list_price)"Total Price",
COUNT(*)"No of Products"
From products;
 Grouping Data – Group by clause
GROUP BY SELECT category_id,COUNT(*)"No of Products",MAX (list_price)"Highest
Price",MIN(list_price)"Lowest Price",AVG(list_price) as "Average
Price",
SUM(list_price)"Total Price"
From products // (__JOIN__ON__)
GROUP BY category_id;
JOIN-HAVING- SELECT store_name, count(*)as "No of Orders"
ORDER BY from stores s JOIN orders o
on s.store_id = o.store_id
group by store_name
having count(*) > 400
order by count(*) desc;

Select Top Records

SELECT TOP SELECT TOP 10 product_name,list_price
Int from products
order by list_price DESC;
SELECT TOP SELECT TOP 10 percent product_name,list_price
Percentage from products
order by list_price DESC;
SELECT TOP INT SELECT TOP 3 WITH TIES product_name,list_price ‫لالظهار االخير المتكرر‬
WITH TIES from products
order by list_price DESC;

Nested queries – Sub queries

‫الظهار اعلي درجة‬ SELECT stdno,mark from register
WHERE mark = (select max(mark) from register);
‫الظهار الطالب الذين‬ Select [Link], firstname, lastname from
‫يدرسون المواد التي‬ students join register on [Link] = [Link]
‫يدرسها خالد‬ where coursed in (select coursed from students join register
on [Link] = [Link] where firstname = 'Khaled');