SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin'
select * from Customers
order by Country ASC, CustomerName DESC;
update Customers
set ContactName = 'Lei', City = 'Gilroy'
Where CustomerID = 1;
update Customers
set contactName = 'Juan'
where Country = 'Mexico'
delete from Customers
where ContactName = 'Alfreds Futterkiste';
delete from Customers;
delete * from Customers;
SELECT columns_name(s)
from table_name
where conditon
limit number;
select * form Customers
where CustomerName LIKE 'a%';
select * form Customers
where CustomerName LIKE '%a';
select * form Customers
where CustomerName LIKE '%or%';
select * form Customers
where CustomerName LIKE '_r%';
select * form Customers
where CustomerName LIKE 'a_%_%';
select * form Customers
where CustomerName NOT LIKE 'a%';
select * form Customers
where City LIKE '[bsp]%';
select * form Customers
where City LIKE '[abc]%';
select * form Customers
where City LIKE '[!abc]%';
SELECT columns_name(s)
from table_name
where column_name in(values, values);
SELECT columns_name(s)
from table_name
where columns_name in (select statement);
select * from Customer
where Country in ('Germany','Grance','UK')
select * from Customers
where Country in (select Country From Suppliers);
select column_name(s)
from table_name
where column_name between value1 and value2;
select * from Products
where Price between 10 and 20;
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1998# AND #08/04/1998#;
SELECT CustomerName, CONCAT(Address, ',', PostalCode, ',', City, ',', Country) as Address
from Customers;
select Orders.OrderID, Customers.CustomerName, Orders.OrderDate
from Orders
inner join Customers on Orders.CustomerID = Customers.CustomerID;
select column_name(s)
from table1
INNER JOIN table2 on table1.column_name = table2.column_name;
selec * Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
From ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID\);
select column_name(s)
from table1
full outer join table2 on table1.column_name = table2.column_name;
select column_name(s)
from table1 t1, table1 t2
where condition
select column_names(s) from table1
where condition
union
sleect column_name(s) from table
where condition;
//allow duplicated values
select column_names(s) from table1
where condition
union all
sleect column_name(s) from table
where condition;
select count(CustomerID) as num, Country
from Customers
Group by Country;
select count(CustomerID) as num, Country
from Customers
Group by Country
Order by num DESC;
select Shippers.ShipperName, count(Orders.OrderID) as num
from Orders
Left join Shippers on Orders.ShipperID = Shippers.ShipperID
Group by shipperName;
SELECT column_name(s)
from table_name
where condition
GROUP BY column_name(s)
having condition // contion for group by
order by column_name(s);
select Country, count(customerID) as num
from Customers
group by Country
having num > 5;
SELECT Employees.LastName, count(Orders.OrderID) as NumberOfOrders
FROM Orders
inner join Employees on Orders.EmployeeId = Employees.EmployeeId
group by Employees.LastName
having NumberOfOrders > 10;
SELECT column_name(s)
from table_name
where exists
(select column from table where condition);
// table operation
SELECT *
INTO newtable in dbname
from oldtable
where condition;
select columns_names(s)
into newtable in dbname
from oldtable
where condition;
insert into table2
select * from table1
where condition;
insert into table2 (column1,columns...)
select column1,columns... from table1
where condition;
create procedure procedure_name
as
sql_statement
go;
exec procedure_name;
create procedure selectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
as
select * FROM Customers WHERE City = @City And PostalCode = @PostalCode
go;
exec selectAllCustomers City = "London", PostalCode = "WAL1 1DP";