BASIC SQL
STATEMENTS
Lab 2
BikeStore Schema
Load BikeStore Database to SQL Server
Data Manipulation Language (DML)
• Select Statement
---select specific columns
SELECT first_name, last_name, ...
FROM [Link];
---select all columns
SELECT * FROM sales. Customers;
--return different countries from table
SELECT DISTINCT Country
FROM Customers;
Data Manipulation Language (DML) /Select
--filter rows based on one or more conditions
SELECT * FROM [Link]
WHERE state = 'CA';
Operator used in Where clause:
= Equal
< Greater than
> Less than
=< Greater than or equal
=> Less than or equal
<> .Not equal
BETWEEN ..AND Between a certain range
LIKE Search for a pattern
( ) IN To specify multiple possible values for a column
Data Manipulation Language (DML) /Select
SELECT * FROM [Link]
WHERE city= 'Orchard Park' AND first_name LIKE 'G%';
SELECT * FROM [Link]
WHERE city= 'Orchard Park' AND first_name LIKE 'G%';
SELECT * FROM [Link]
WHERE NOT City = 'Spain';
-- sort the result-set in ascending (default )or descending order
SELECT * FROM [Link]
ORDER BY List_Price DESC;
-- sort the result-set in ascending (default )or descending order
SELECT city, first_name, last_name
FROM [Link]
ORDER BY city, first_name;
DML (SELECT / Sorting)
• Sort a result set by an expression
SELECT first_name, last_name FROM [Link]
ORDER BY LEN(first_name) DESC;
• Sort by ordinal positions of columns
SELECT first_name, last_name FROM [Link]
ORDER BY 1,2 DESC;
DML (SELECT / grouping)
• Group rows into groups For example, the following
statement returns all the cities of customers located in
California and the number of customers in each city.
SELECT city, COUNT (*)
FROM [Link]
WHERE state = 'CA'
GROUP BY city
ORDER BY city;
SELECT city, COUNT (*)
FROM [Link]
WHERE state = 'CA'
GROUP BY city
HAVING COUNT (*) > 10
ORDER BY city;
DML (Select/top)
• The SELECT TOP clause is used to limit the rows returned by query.
--Using TOP with a constant value
SELECT top (3) * FROM [Link] ORDER BY
LEN(first_name) DESC;
--Using TOP to return a percentage of rows
SELECT TOP 1 PERCENT product_name, list_price
FROM [Link]
--Using TOP WITH TIES to include rows that match the values in the
last row
SELECT top 3 with ties product_name,list_price
FROM [Link]
ORDER BY list_price DESC;
DML (SELECT /inner Join)
SELECT product_name,category_name, list_price
FROM [Link] p INNER JOIN [Link] c
ON c.category_id = p.category_id
ORDER BY product_name DESC;
DML (SELECT /left Join)
We need to retrieve all product with it’s order
number
SELECT product_name, order_id
FROM [Link] p LEFT JOIN
sales.order_items o
ON o.product_id = p.product_id
ORDER BY product_name;
SELECT product_name, order_id
FROM [Link] p LEFT JOIN
sales.order_items o
ON o.product_id = p.product_id
ORDER BY order_id;
DML (SELECT /right Join)
SELECT product_name, order_id
FROM sales.order_items o RIGhT JOIN [Link] p
ON o.product_id = p.product_id
ORDER BY order_id;
Retrieve the products that do not have any
sales
SELECT product_name, order_id
FROM sales.order_items o RIGhT JOIN [Link] p
ON o.product_id = p.product_id
Where order_id is null
ORDER BY order_id;
DML (SELECT /full outer Join)
• Emp (Emp_ID, Emp_Name, Salary ,Dept_ID)
• Dept (Dept_ID, Dept_Name ,Magr_ID )
• Retrieve all employees name with their Department Name and also the
departments and employees who have not hired yet.
SELECT e.Emp_name EMP , d.Dept_name DEPT
FROM EMP e FULL OUTER JOIN DEPT d
ON e.Dept_id=d.dept_id;
SQL query execution order
DML(Insert)
• Insert syntax
INSERT INTO table_name (column_list)
VALUES (value_list);
• Insert New customer
--insert specific columns
insert into
[Link](first_name,last_name,email,state)
values ('Ali','Ahmed','AhmedAli@[Link]', 'Cairo');
--insert All columns
insert into [Link]
values ('Tameka','Fisher',NULL,'[Link]@[Link]','769C Honey
Creek St. ','Redondo Beach','CA',90278)
DML(Insert statement )
• Insert multiple Row
insert into [Link]
values ('Tameka','Fisher',NULL,'tameka@[Link]','76 Honey St.','Redondo
Beach','CA',90278),
(‘Ali’,’Fadi',NULL,’tani@[Link]',’76 Saad St. ','Redondo Beach','CA’,2020),
(‘Tami','Fisher',NULL,'[Link]@[Link]’,Null 'Redondo Beach','CA’,2024)
• Insert all rows from another table example
--inserts all addresses from the customers table into the addresses table
INSERT INTO [Link] (street, city, state, zip_code)
SELECT street,city,state, zip_code
FROM [Link]
DML(Insert statement )
• Insert the top N of rows
INSERT top (5) into [Link] (street, city, state, zip_code)
SELECT street,city,state, zip_code
FROM [Link]
• Insert the top percent of rows
INSERT top (5)percent into [Link] (street, city, state, zip_code)
SELECT street,city,state, zip_code
FROM [Link]
DML (update statement)
• Update all rows in the table
update [Link]
set list_price=1500;
• Update specific rows
update [Link]
set list_price=1500
Where product_id =100
DML (DELETE Statement)
• Delete all table rows
delete from [Link]
• Delete specific rows
delete from [Link]
Where product_id =100
• Delete the number of random rows
Delete top (5) from [Link]
Where product_id =100
• Delete the percent of random rows
Delete top (5) percent from [Link]
Where product_id =100
DDL (CREATE Statement )
• Create New database
CREATE DATABASE FCI_ADMIN;
• Create New Table
create table Employee
(
emp_id int primary key identity (10,10),
first_name varchar (20) not null,
second_name varchar (20) not null,
hire_date DATETIME ,
dept_id int not null,
FOREIGN KEY (dept_id) REFERENCES Department (D_ID)
);
DDL (ALTER Statement)
• General Syntax
ALTER TABLE table_name
ADD column_name_1 data_type_1 column_constrain_1,
column_name_2 data_type_2 column_constraint_2,
• Add New Column to existing Table
ALTER TABLE [Link]
ADD birthdate datetime NULL;
DDL (ALTER Statement)
• Alter statement used to modify a column of a table.
1. Modify the data type
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type(size);
2. Change the size
ALTER TABLE t2 ALTER COLUMN c VARCHAR (50);
3. Add not null constrain
ALTER TABLE [Link]
alter column email varchar(250) not null
DDL (ALTER Statement)
• Alter can be used to drop column from table
ALTER TABLE table_name
DROP COLUMN column_name1,column_name2;
• Alter can be used to drop constraints from table
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
DDL(DROP Statement)
• DROP TABLE statement to remove one or more tables from a database.
DROP TABLE IF EXISTS [Link];
DDL(TRUNCATE Statement)
• TRUNCATE used to delete all rows from a table. In this case, you typically
use the DELETE statement without a where clause.
TRUNCATE TABLE table_name;
Thank you
☺