SQL 명령어 SELECT 구문

CREATE DATABASE test ON

(filename='c:\program files\microsoft sql server\mssql\data\test.mdf', name='testData')

LOG ON
(filename='c:\program files\microsoft sql server\mssql\data\test.ldf', name='testLog')

CREATE TABLE member (
uid varchar(12) primary key,
pwd varchar(12) not null
)

DROP TABLE member 

SELECT * FROM member

USE pubs 

SELECT * FROM jobs

SELECT stor_id, ord_num, qty, title_id FROM sales 

SELECT * FROM sales WHERE title_id = 'BU1032' OR qty >= 20

SELECT * FROM authors WHERE state IN ('CA', 'MI') 

SELECT * FROM sales WHERE qty BETWEEN 10 AND 25 

SELECT * FROM sales WHERE title_id LIKE '%ps%' 

SELECT * FROM authors ORDER BY au_fname DESC

SELECT * FROM sales WHERE qty BETWEEN 10 AND 20 ORDER BY qty ASC 

SELECT * FROM sales ORDER BY stor_id, title_id, ord_date desc 

SELECT TOP 5 * FROM authors 

SELECT TOP 6 * FROM sales ORDER BY qty DESC 

SELECT TOP 5 PERCENT * FROM authors 

SELECT TOP 5 PERCENT * FROM sales ORDER BY qty ASC 

SELECT title_id FROM sales ORDER BY title_id 

SELECT COUNT(title_id) FROM sales 

SELECT AVG(qty) FROM sales 

SELECT COUNT(au_id), state FROM authors GROUP BY state ORDER BY COUNT(au_id) DESC
: group by를 사용하는 select문에는 반드시 group으로 묶어 주는 필드를 앞에 명시.

SELECT title_id FROM sales GROUP BY title_id ORDER BY SUM(qty) ASC

SELECT SUM(qty), title_id FROM sales GROUP BY title_id HAVING SUM(qty) >= 30 ORDER BY sum(qty)

SELECT stor_id, AVG(qty) AS avgQty FROM sales GROUP BY stor_id having AVG(qty) < 40 ORDER BY avgQty 

SELECT au_fname + ' , ' + au_lname AS fullName FROM authors