SELECT
SELECT SQL. SELECT
SELECT * FROM PC; |
PC. . , SELECT:
SELECT price, speed, hd, ram, cd, model, code FROM Pc; |
C , . , , :
SELECT speed, ram FROM PC; |
, , , . PC code, . , (, 1 3). (, , ), DISTINCT:
SELECT DISTINCT speed, ram FROM Pc; |
, , SELECT. ORDER BY < >, SELECT. , SELECT. ,
SELECT DISTINCT speed, ram FROM Pc ORDER BY ram DESC |
SELECT DISTINCT speed, ram FROM Pc ORDER BY 2 DESC |
( ASC ) ( DESC).
SELECT DISTINCT speed, ram FROM Pc ORDER BY ram DESC, speed DESC |
WHERE <>, FROM. , TRUE. . , " $500" :
SELECT DISTINCT speed, ram FROM Pc WHERE price<500 ORDER BY 2 DESC |
, . : =, >, <, >=, <=, <>.
NUMERIC () .
CHARACTER STRING ( ) . a1a2an b1b2bn - , "" , 1<b1, 1=b1 2<b2 .. , 12n<b1b2bm, n<m 12n=b1b2bn, .. . , 'folder'<'for', .. , 'folder' 'for'. 'bar' < 'barber', .
DATETIME (/) .
INTERVAL ( ) , NUMERIC.
|
|
. , 500 $800:
SELECT * FROM Pc WHERE speed >= 500 AND price < 800; |
. , $300:
SELECT * FROM Printer WHERE NOT (type = 'matrix') AND price < 300; |
BETWEEN
BETWEEN , , , AND. , , BETWEEN .
BETWEEN::=
< > [NOT] BETWEEN
< > AND < >
exp1 BETWEEN exp2 AND exp3
exp1>=exp2 AND exp1<=exp3
exp1 NOT BETWEEN exp2 AND exp3
NOT (exp1 BETWEEN exp2 AND exp3)
exp1 BETWEEN exp2 AND exp3 TRUE, , exp1 BETWEEN exp3 AND exp2 TRUE,
exp1>=exp2 AND exp1<=exp3
exp1>=exp3 AND exp1<=exp2
. $400 $600:
SELECT model, speed FROM Pc WHERE price BETWEEN 400 AND 600; |
IN
IN , , , . SELECT, , . IN , IN TRUE. IN <>X, FALSE. , ( ), FALSE. , UNKNOWN.
IN::=
< > [NOT] IN (<>)
| (< >,...)
|
|
. , , 10 20 :
SELECT model, speed, hd FROM Pc WHERE hd IN (10, 20); |
. , , 10 20 :
SELECT model, speed, hd FROM Pc WHERE hd IN (10, 20) AND model IN (SELECT model FROM product WHERE maker = 'A'); |
, SELECT, . , . AS, , , .. .
,
SELECT ram AS Mb, hd Gb FROM Pc WHERE cd = '24x'; |
SELECT . , . , FROM, . , , , :
SELECT ram * 1024 AS Kb, hd Gb FROM Pc WHERE cd = '24x'; |
. , . ,
SELECT ram, 'Mb' AS ram_units, hd, 'Gb' AS hd_units FROM Pc WHERE cd = '24x'; |
LIKE
. , '':
SELECT * FROM Ships WHERE class LIKE '%o'; |
. , '', 'go':
SELECT * FROM Ships WHERE class NOT LIKE '%go' AND class LIKE '%o'; |
IS [NOT] NULL
() . , .. NULL UNKNOWN ().
, Pc, price ( ), :
SELECT * FROM Pc WHERE price IS NULL; |
. :
SELECT MIN(price) AS Min_price, MAX(price) AS Max_price FROM PC; |
. , :
SELECT COUNT(*) AS Qty FROM PC WHERE model IN (SELECT model FROM Product WHERE maker = 'A'); |
. , , ( , Product ):
SELECT COUNT(model) AS Qty_model FROM Product WHERE maker = 'A'; |
. , . , , . PC (.. ).
, , DISTINCT. ALL . ,
SELECT COUNT(DISTINCT model) AS Qty FROM PC WHERE model IN (SELECT model FROM Product WHERE maker = 'A'); |
GROUP BY
GROUP BY , (COUNT, MIN, MAX, AVG SUM). , , , SELECT, , , . SELECT, , GROUP BY. , . . , GROUP BY NULL , .. , NULL-, .
GROUP BY, SELECT , . , DISTINCT, .
:
|
|
SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model; |
. , $800:
SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model HAVING AVG(price) < 800; |
. , $600:
SELECT DISTINCT PC.model, maker FROM PC, Product WHERE PC.model = Product.model AND price < 600; |
. , :
SELECT DISTINCT A.model AS model_1, B.model AS model_2 FROM PC AS A, PC B WHERE A.price = B.price AND A.model < B.model; |
, FROM . , :
SELECT DISTINCT PC.model, maker FROM PC, (SELECT maker, model FROM Product) AS prod WHERE PC.model = prod.model AND price < 600; |
. , , :
SELECT maker, Product.model AS model_1, PC.model AS model_2, price FROM Product INNER JOIN PC ON PC.model = Product.model ORDER BY maker, PC.model; |
. , :
SELECT maker, Product.model AS model_1, PC.model AS model_2, price FROM Product LEFT JOIN PC ON PC.model = Product.model WHERE type = 'PC' ORDER BY maker, PC.model; |
RIGHT JOIN LEFT JOIN, .. , , .
Product LEFT JOIN PC ON PC.model = Product.model
PC RIGHT JOIN Product ON PC.model = Product.model
SELECT maker, Product.model AS model_1, PC.model AS model_2, price FROM Product RIGHT JOIN PC ON PC.model = Product.model ORDER BY maker, PC.model; |
. -:
SELECT model, price FROM PC UNION SELECT model, price FROM Laptop ORDER BY price DESC; |
. , -:
SELECT Product.type, PC.model, price FROM PC INNER JOIN Product ON PC.model = Product.model UNION SELECT Product.type, Laptop.model, price FROM Laptop INNER JOIN Product ON Laptop.model = Product.model ORDER BY price DESC; |
. -, :
|
|
SELECT DISTINCT maker FROM Product AS Lap_product WHERE type = 'Laptop' AND EXISTS (SELECT maker FROM Product WHERE type = 'Printer' AND maker = Lap_product.maker); |
. -, :
SELECT DISTINCT maker FROM Product AS Lap_product WHERE type = 'Laptop' AND NOT EXISTS (SELECT maker FROM Product WHERE type = 'Printer' AND maker = Lap_product.maker); |
. , (.. PC):
SELECT DISTINCT maker FROM Product WHERE type = 'PC' AND NOT model = ANY (SELECT model FROM PC); |
. -, :
SELECT DISTINCT model, price FROM Laptop WHERE price > ALL (SELECT price FROM PC); |
. , -:
SELECT DISTINCT model, price FROM PC WHERE price > (SELECT MIN(price) FROM Laptop); |
. , , -, 600 .
, , :
SELECT prod.maker, lap.* FROM (SELECT 'Laptop' AS type, model, speed FROM Laptop WHERE speed > 600) AS lap INNER JOIN (SELECT maker, model FROM Product) AS prod ON lap.model = prod.model; |
. - , .. - , .
SELECT:
SELECT (SELECT AVG(price) FROM Laptop) - (SELECT AVG(price) FROM PC) AS dif_price; |
. - " = ".
SELECT ' = ' + CAST(AVG(price) AS CHAR(15)) FROM laptop; |
: Ships.
SELECT AVG(launched) FROM ships; |
, , ? CAST . ,
SELECT CAST(AVG(launched) AS NUMERIC(6,2)) FROM ships; |
1926.00. , CAST :
SELECT AVG(CAST(launched AS NUMERIC(6,2))) FROM ships; |
- 1926.238095. . , . :
SELECT CAST(AVG(CAST(launched AS NUMERIC(6,2))) AS NUMERIC(6,2)) FROM ships; |
, - 1926.24. . :
SELECT CAST(AVG(launched*1.0) AS NUMERIC(6,2)) FROM ships; |
.. (EXACT NUMERIC), , .
CONVERT:
SELECT CONVERT(NUMERIC(6,2),AVG(launched*1.0)) FROM ships; |
CONVERT CAST , (, datetime) . .
SELECT CONVERT(char(25),CONVERT(datetime,'20030722')); |
. ( ), : " ".
:
SELECT DISTINCT product.model, price FROM product LEFT JOIN pc c ON product.model=c.model WHERE product.type='pc'; |
NULL- , CASE:
SELECT DISTINCT product.model, CASE WHEN price IS NULL THEN ' ' ELSE CAST(price AS CHAR(20)) END price FROM product LEFT JOIN pc c ON product.model=c.model WHERE product.type='pc' |
23/01/2004, , .
SELECT DATEADD(day, 7, current_timestamp) |
SELECT DATEADD(ww, 1, current_timestamp) |
; - 2004-01-30 19:40:58.923.
|
|
SELECT DATEADD(mm, 1/4, current_timestamp) |
, datepart , 0 , , .
, CURRENT_TIMESTAMP T-SQL GETDATE() . , , .
( 4). , .
SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip)) |
, .. datetime.
DATEDIFF
DATEDIFF (datepart, startdate, enddate)
, - startdate ( ) enddate ( ). . datepart DATEADD.
( 4). , .
SELECT DATEDIFF(dd, (SELECT MIN(date) FROM pass_in_trip), (SELECT MAX(date) FROM pass_in_trip)) |
, / , . / , - . , 1123
SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) FROM trip WHERE trip_no=1123 |
SELECT DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) FROM trip WHERE trip_no=1123 |
, . , , . (24*60 = 1440 ).
SELECT CASE WHEN time_dep>=time_arr THEN time_arr-time_dep+1440 ELSE time_arr-time_dep END dur FROM (SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep, DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr FROM trip WHERE trip_no=1123) tm |
, CASE, . , , .
(4 ). 1123.
Pass_in_trip , , .. . , Pass_in_trip, Trip
SELECT pt.trip_no, DATEADD(mi, DATEPART(hh,time_out)*60 + DATEPART(mi,time_out), date) [time] FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no WHERE t.trip_no=1123 |
Product, CREATE TABLE:
CREATE TABLE [dbo].[product] ([maker] [char] (1) NOT NULL, [model] [varchar] (4) NOT NULL, [type] [varchar] (7) NOT NULL) |
1157 B. :
INSERT INTO Product VALUES ('B', 1157, 'PC'); |
, "" :
INSERT INTO Product (type, model, maker) VALUES ('PC', 1157, 'B'); |
, , . , . :
CREATE TABLE [product_D] ([maker] [char] (1) NULL, [model] [varchar] (4) NULL, [type] [varchar] (7) NOT NULL DEFAULT 'PC') |
, ( - NULL, - type - 'PC'). :
INSERT INTO Product_D (model, maker) VALUES (1157, 'B'); |
Product Product_Inc, code:
SET IDENTITY_INSERT Printer_Inc ON; INSERT INTO Printer_Inc(code, model,color,type,price) SELECT * FROM Printer; |
"" . , - 10 :
UPDATE Laptop SET price=price*0.9 |
. , , 10 -. RAM, . :
UPDATE Laptop SET hd=ram/2 WHERE hd<10 |
, CASE. , , 20 - 128 40 - -, :
UPDATE Laptop SET hd = CASE WHEN ram<128 THEN 20 ELSE 40 END |
. , - . :
UPDATE Laptop SET speed = (SELECT MAX(speed) FROM Laptop) |
. code Laptop IDENTITY(1,1),
UPDATE Laptop SET code=5 WHERE code=4 |
, .. , . , . , SET IDENTITY_INSERT, :
SET IDENTITY_INSERT Laptop ON INSERT INTO Laptop_ID(code, model, speed, ram, hd, price, screen) SELECT 5, model, speed, ram, hd, price, screen FROM Laptop_ID WHERE code=4 DELETE FROM Laptop_ID WHERE code=4 |
. "No PC" ( ) type Product, PC. :
UPDATE Product SET type='No PC' FROM Product pr LEFT JOIN PC ON pr.model=pc.model WHERE type='pc' AND pc.model IS NULL |
, pc.model , PC, NULL-, . , "" :
UPDATE Product SET type='No PC' WHERE type='pc' and model NOT IN (SELECT model FROM PC) |
. Laptop - 12 .
DELETE FROM Laptop WHERE screen<12 |
DELETE FROM Laptop |
TRUNCATE TABLE Laptop |
Product, PC.
, :
DELETE FROM Product WHERE type='pc' AND model NOT IN (SELECT model FROM PC) |
FROM :
DELETE FROM Product FROM Product pr LEFT JOIN PC ON pr.model=pc.model WHERE type='pc' AND pc.model IS NULL |