sql基础语句2

1.SELECT A1.store_name Store, SUM(A1.Sales) “Total Sales”
FROM Store_Information A1
GROUP BY A1.store_name

2.SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
【内连接】left join：两个表格内都有相同的值，那一笔资料才会被选出。

3.SELECT A1.store_name, SUM(A2.Sales) SALES
FROM Georgraphy A1, Store_Information A2
WHERE A1.store_name = A2.store_name (+)
GROUP BY A1.store_name
【外连接】

4.SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = ‘Boston’;

5.SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = ‘Los Angeles’;

6.SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = ‘San Diego’;

7.SELECT TRIM(’ Sample ');

8.SELECT LTRIM(’ Sample ');

9.SELECT RTRIM(’ Sample ');

10.CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

11.CREATE TABLE Customer
(SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar(30));

12.CREATE TABLE Customer
(SID integer Unique,
Last_Name varchar (30),
First_Name varchar(30));

13.CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30));

14.CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));

15.ALTER TABLE Customer ADD PRIMARY KEY (SID);

16.CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));

17.ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);

18.CREATE INDEX IDX_CUSTOMER_LAST_NAME
on CUSTOMER (Last_Name)

19.ALTER table customer add Gender char(1)

20.ALTER table customer change Address Addr char(50)

21.ALTER table customer modify Addr char(30)

22.ALTER table customer drop Gender

23.DROP TABLE customer.

24.TRUNCATE TABLE customer.

25.INSERT INTO Store_Information (store_name, Sales, Date)
VALUES (‘Los Angeles’, 900, ‘Jan-10-1999’)

26.INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

27.UPDATE Store_Information
SET Sales = 500
WHERE store_name = “Los Angeles”
AND Date = “Jan-08-1999”

28.DELETE FROM Store_Information
WHERE store_name = “Los Angeles”