- Select all records from the Customers table, sort the result alphabetically, first by the column Country, then, by the column City.
SELECT * FROM Customers
ORDER BY Country,City;
- Select all records from the Customers where the PostalCode column is empty.
SELECT * FROM Customers
WHERE postalcode is null;
- Select all records from the Customers where the PostalCode column is NOT empty.
SELECT * FROM Customers
WHERE
postalcode is not null;
- Update the City column of all records in the Customers table.
UPDATE Customers
SET City = 'Oslo';
- Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value "Norway".
update Customers set City = 'Oslo' where Country = 'Norway';
- Update the City value and the Country value.
update Customers set City = 'Oslo' , country = 'Norway' WHERE CustomerID = 32;
- Delete all the records from the Customers table where the Country value is 'Norway'.
delete from Customers where Country = 'Norway';
- Delete all the records from the Customers table.
delete from Customers; // delete * from customer 가 아님
- Use the MIN function to select the record with the smallest value of the Price column.
SELECT min(price) FROM Products; //sum,avg,min,max ...
- Select all records where the value of the City column ends with the letter "a".
SELECT * FROM Customers where city like '%a';
- Select all records where the value of the City column contains the letter "a".
SELECT * FROM Customers where city like '%a%';
- Select all records where the value of the City column starts with letter "a" and ends with the letter "b".
SELECT * FROM Customers where city like 'a%b';
- Select all records where the value of the City column does NOT start with the letter "a".
SELECT * FROM Customers where NOT city like 'a%';
- Select all records where the second letter of the City is an "a".
두 번째 글자가 'a'인 것
SELECT * FROM Customers WHERE City LIKE '_a%';
- Select all records where the first letter of the City is an "a" or a "c" or an "s".
첫 번째 글자가 a 또는 c 또는 s로 시작하는 것.
SELECT * FROM Customers WHERE City LIKE '[acs]%';
- Select all records where the first letter of the City starts with anything from an "a" to an "f".
SELECT * FROM Customers WHERE City LIKE '[a-f]%';
- Select all records where the first letter of the City is NOT an "a" or a "c" or an "f".
SELECT * FROM Customers WHERE City LIKE '[!acf]%';
- 다음 SQL 문은 "독일", "프랑스" 또는 "영국"에 있는 모든 고객을 선택합니다.
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
- Use the IN operator to select all the records where Country is NOT "Norway" and NOT "France".
SELECT * FROM Customers where country not in ('Norway', 'France');
- Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20.
SELECT * FROM Products WHERE Price between 10 and 20 ;
- use the BETWEEN operator to select all the records where the value of the Price column is NOT between 10 and 20.
SELECT * FROM Products WHERE Price not between 10 and 20;
- When displaying the Customers table, make an ALIAS of the PostalCode column, the column should be called Pno instead.
SELECT CustomerName, Address, PostalCode as Pno FROM Customers;
*Insert the missing parts in the JOIN clause to join the two tables Orders and Customers, using the CustomerID field in both tables as the relationship between the two tables.
SELECT * FROM Orders LEFT JOIN Customers on orders.customerid = customers.customerid;
- Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.
SELECT * FROM Orders right join customers ON Orders.CustomerID=Customers.CustomerID;
- List the number of customers in each country.
SELECT count (CustomerID), Country FROM Customers group by country;
- List the number of customers in each country.
SELECT count (CustomerID), Country FROM Customers group by country;
- List the number of customers in each country, ordered by the country with the most customers first.
SELECT COUNT (CustomerID), Country FROM Customers GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
- Use the TRUNCATE statement to delete all data inside a table.
모든 행 삭제
TRUNCATE TABLE Persons;
- add a column of type DATE called Birthday.
alter table Persons add birthday date;
- Delete the column Birthday from the Persons table.
alter table Persons drop column Birthday;
