SQL 1 SQL Transact-SQL Transact-SQL Transact-SQL SQL Query Analyzer SQL Query Analyzer SQL Query Analyzer osql Transact-SQL SELECT WHERE osql 1.1 Transact-SQL Transact-SQL SQL Server (ANSI) (ISO) SQL SQL-92 ANSI ISO 1992 SQL Transact-SQL SQLServer2000 SQL-92 ANSI-SQL Transact-SQL ANSI-SQL Transact-SQL 1.2 Transact-SQL SQL Server SELECT SELECT
2 MCSE 2000 SQL 2000 Transact-SQL SQL Server Transact-SQL (DDL) (DCL) (DML) (DML) 1.2.1 (DDL) (DDL) DDL DDL CREATE ALTER DROP sysadmin dbcreator db_owner db_ddladmin role DDL DDL Northwind customer cust_id, company contact phone CTRATE TABLE customer (cust_id int, company varchar(40), contact varchar(30), phone char(12)) 1.2.2 (DCL) (DCL) GRANT DENY REVOKE Transact-SQL
SQL 3 sysadmin dbcreator db_owner db_secudtyadmin public products USE nothwind GRANT SELECT ON products TO public 1.2.3 (DML) (DML) DML SELECT INSERT UPDATE DELETE sysadmin dbcreator db_owner db_datawriter Northwind category ID product name product ID unitprice SELECT category ID, product name, product ID, unitprice FROM products 1.3 Transact-SQL DML Transact-SQL directives Comments Identifiers Variables 1.3.1 SQL Server Transact-SQL SQL Server SQL Server SQL Server SQL Server Transact-SQL SQL Server Transact-SQL Transact-SQL ad hoc Transact-SQL
4 MCSE 2000 SQL 2000, Transact-SQL ( ) Transact-SQL 1.3.2 EXEC EXEC Transact-SQL EXEC 1.3.3 SQL 1 (--) Transact-SQL SELECT productname (unitsinstock - unitsonorder) -- Calculates inventory supplierid FROM products SELECT productname (unitsinstock - unitsonorder) -- Calculates inventory -- supplierid FROM products
SQL 5 2 (/*) (*/) SQL * This code retrieves all rows of the products table and displays the unit price the unit price increased by 10 percent and the name of the product * SELECT unitprice (unitprice * 1.1) productname FROM products SQL /* DECLARE @v1 int SET @v1=0 WHILE @v1<100 */ BEGIN SELECT @v1=(@v1+1) SELECT @v1 1.3.4 SQL Server 1
6 MCSE 2000 SQL 2000 1 128 (_ @ #) 2 Transact-SQL ([]) SELECT * FROM [Blanks In Table Name] SET QUOTED_IDENTIFER ( ) SELECT * FROM Blanks in Table Name SET QUOTED_IDENTIFIER on 3 1.3.5 Transact-SQL SQL SQL Server ( ) 1
SQL 7 int tinyint smallint bigint numeric decimal money smallmoney float real 2 datetime 0 333 smalldatetime 1 3 char nchar varchar nvarchar 4 binary varbinary bit 1 0 1 5 (Unique Identifiers) uniqueidentifier (GUID) 16 16 6 SQL SQL Server text ntext rowversion Sql_variant 7 (image&text) (BLOB) Unicode Unicode image text ntext 8 Tables 9 (Cursors) (DDL)
8 MCSE 2000 SQL 2000 10 (Nullability) 1.3.6 SQL Transact-SQL DECLARE SET SELECT @ @ DECLARE{@10cal_variabledata_type}[,...n] SET@local_variable_name=expression @EmpID @vlname @vlname @vlname Northwind@EmpID DECLARE @EmpID varchar(11) @vlname char(20) SET @vlname= Dodsworth SELECT @EmpID=employeeid FROM employees WHERE LastName=@vlname SELECT @EmpID AS EmployeeID EmployeeID 9 (1row(s)affected) 1.3.7 SELECT Transact-SQL Transact-SQL
SQL 9 1 (Aggregate) products unitprice SELECT AVG(unitprice) AS AvgPrice FROM products AvgPrice 28 8663 (1row(S)affected) 2 (scalar) (char varchar ) SQL Server SELECT DB_NAME() AS database database
10 MCSE 2000 SQL 2000 Northwind (1row(s)affected) 3 (Rowset) Transact-SQL (table reference) titles SELECT * FROM OPENQUERY(OracleSvr, SELECT name id FROM owner titles ) SELECT 'ANSI:', CONVERT(varchar(30), GETDATE(), 102) AS Style UNION SELECT 'Japanese:', CONVERT(varchar(30), GETDATE(), 111) UNION SELECT 'European:', CONVERT(varchar(30), GETDATE(), 113) 1.3.8 {constant/column_name/function/ (subquery)} [{arithmetic_operator/string_operator/ AND OR NOT} {constant/column_name/function/ (subquery)} } 1 SQL Server Transact-SQL (+) (-) (*) (/) ( )( )
SQL 11 = < >= <= <> (+) (AND) (OR) (NOT) WHERE 2 ( ) SQL Server ( ) () * -+ + NOT AND OR SQL Server SQL Server 1.3.9
12 MCSE 2000 SQL 2000 10 000 SELECT OrderID, ProductID, (UnitPrice * Quantity) AS ExtendedAmount FROM [Order Details] WHERE (UnitPrice * Quantity) > 10000 OrderlD ProductID ExtendedAmount 10353 38 10540 0000 10417 38 10540 0000 10424 38 10329 2000 10865 38 15810 0000 10889 38 10540 0000 10981 38 15810 0000 (6 row(s) affected) 1.3.10 Transact-SQL CASE SELECT UPDATE 1Statement level BEGIN END, Transact-SQL IF ELSE, SQL Server IF ELSE WHILE, WHILE BREAK CONTINUE WHILE IF EXISTS (SELECT * FROM orders
SQL 13 WHERE customerid = fran ) PRINT *** Customer cannot be deleted *** ELSE BEGIN DELETE customers WHERE customerid = frank PRINT *** Customer deleted *** END 2 Row level CASE true CASE WHEN false ELSE SQL Server ELSE CASE CASE expression END {WHEN expression THEN result} [ n] [ELSE result] 4 5 6 WHILE DECLARE @n tinyint SET @n = 5 IF (@n BETWEEN 4 and 6) BEGIN WHILE (@n > 0) BEGIN END SELECT @n AS Number CASE WHEN (@n 2) = 1 THEN ELSE EVEN END AS Type SET @n = @n - 1 ODD
14 MCSE 2000 SQL 2000 ELSE END PRINT NO ANALYSIS Number Type 5 EVEN (1 row(s) affected) Number Type 4 ODD (1 row(s) affected) Number Type 3 EVEN (1 row(s) affected) Number Type 2 ODD (1 row(s) affected) Number Type 1 EVEN (1 row(s) affected) (BEGIN END) 1.3.11 SQL Server osql SQL Query Analyzer DUMP BACKUP SQL Server Transact-SQL SQL Server Transact-SQL Transact-SQL
SQL 15 1.4 SQL Query Analyzer(SQL ) SQL Server SQL Query Analyzer, Transact-SQL SQL Query Analyzer SQL Server Transact-SQL SQL Query Analyzer SQL Query Analyzer SQL SQL Server SQL Server SQL 1.5 SQL Query Analyzer SQL Query Analyzer SELECT CREATE INSERT
16 MCSE 2000 SQL 2000 ALTER Transact-SQL Transact-SQL 1.6 SQL Query Analyzer SQL Query Analyzer SQL Server Templates SQL Query Analyzer SQL Query Analyzer <parameter_name, data_type, value> parameter_name data_type value Replace Template Parameters 1.7 osql osql Transact-SQL osql Transact-SQL osql SQL Server ODBC( ) ODBC (APl) osql Transact-SQL SQL Server osql 1 (Interactive Mode)
SQL 17 osql Transact-SQL SQL Server Transact-SQL Qtilt EXIT osql 2 (Script Execution Mode) Transact-SQL osql osql Transact-SQL ( * txt) Osql osql [-Sserver_name] [-E] [-U login_id] [-P password] [-I input_file] [-O output_file][-?] osql -S server_name SQL Server server_name SQL Server osql -E -U login_id ID -U-P SQL Server -P password -P osql -P osql NULL -P -U SQL Server - i input=file Transact-SQL -o output_file osql -o Unicode Unicode 1.8 Transact-SQI Transact-SQL Transact-SQL
18 MCSE 2000 SQL 2000 1.8.1 Transact-SQL Transact-SQL Transact-SQL EXECUTE SQL EXECUTE ({@str_var tsql string'}[{@str_var tsql_string'} ])} northwind SQL USE library DECLARE @dbname varchar(30) @tablename varchar(30) SET @dbname = northwind SET @tablename = products EXECUTE ( USE +@dbname+ SELECT productname FROM + @tablename) productname Chaj Chang Anise Syrup EXECUTE{ SET NOCOUNTON + SELECT lastname reportsto FROM employees
SQL 19 WHERE reportsto IS NULL ) Iastname Fuller reportsto NULL 1.8.2 (Batches) 1 Transact-SQL Transact-SQL 2 Transact-SQL SQL Query Analyzer osql ODBC ODBCAPI 3 SQL Server SQL Server 4 AS CREATE PROCEDURE CREATE VIEW CREATE TRIGGER CREATE RULE AS CREATE DEFAULT
20 MCSE 2000 SQL 2000 CREATE VIEW CREATE DATABASE CREATE TABLE CREATE VIEW CREATE VIEW CREATE DATABASE CREATE TABLE CREATE VIEW CREATE VIEW 1.8.3 Transact-SQL Transact-SQL SQL Query Analyzer.sql SQL Query Analyzer osql 1.9 SELECT SELECT 1.9.1 SELECT SELECT SELECT [ALL DISTINCT]<select_list>
SQL 21 FROM{<table_source>}[ n) [WHERE<search_condition>] select_list WHERE WHERE FROM 1.9.2 (select list) ( ) <select_list> = { * {table_name view_name table_alias}.* {column_name expression IDENTITYCOL ROWGUIDCOL} [[AS]column_alias] column_alias=expression } [ n] *, employees employeeid lastname firstname title SELECT employeeid lastname firstname title FROM employees employeeid lastname firstname title 1 Davolio Nancy SalesRepresentative 2 Fuller Andrew VicePresident Sales 3 Leverling Janet SalesRepresentative
22 MCSE 2000 SQL 2000 4 Peacock Margaret SalesRepresentative 5 Buchanan Steven SalesManager 6 Suyama Michael SalesRepresentative 7 King Robert SalesRepresentative 8 Callahan Laura lnsidesalescoordinator 9 Dodsworth Anne SalesRepresentative (9 row(s) affected) 1.9.3 WHERE WHERE WHERE <search_condition>::= {[NOT]<predicate> (<search_condition>)} [{AND OR}[NOT]{<predicate> (<search_condition>)}] }[ n] WHERE char nchar varchar nvarchar text datetime smalldatetime ( ) SELECT WHERE employees employeeid lastname firstname title employeid 5 SELECT employeeid lastname firstname title FROM employees WHERE employeeid = 5 employeeid lastname firstname title 5 Buchanan Steven SalesManager (1 row(s) affected) 1.10 WHERE
SQL 23 SELECT > < >= <= and<> = LIKE NOTLIKE AND OR NOT NOTBETWEEN NOTIN IS NULL NOT NULL 1.10.1 = > < >= <= <> NOT employees SELECT lastname city FROM employees WHERE country= USA Iastname city
24 MCSE 2000 SQL 2000 Davolio Fuller Leverling Peacock Callahan (5 row(s) affected) Seattle Tacoma Kirkland Redmond Seattle orders 1996 8 1 orderid customerid SELECT orderid customerid FROM orders WHERE orderdate < 8 1 96 orderid 10248 VINET 10249 TOMSP 10250 HANAR customerid 10251 VICTE 10252 SUPRD 10253 HANAR... (22 row(s) affected) 1.10.2 LIKE LIKE LIKE char nchar varchar nvarchar datetime LIKE
SQL 25 LIKE BR LIKE Br% LIKE een BR Br een LIKE en en LIKE _en LIKE [CK] LIKE [S-V]ing en C K ing S-V Customers Res SELECT companyname FROM customers WHERE companyname LIKE Restaurant companyname GROSELLA-Restaurante LonesomePineRestaurant TortugaRestaurante (3 row(s) affected) 1.10.3 AND OR NOT AND OR NOT 1 (parentheses)
26 MCSE 2000 SQL 2000 2 SQLServer2000 NOT AND OR T 46 16 SELECT productid productname supplierid unitprice FROM products WHERE(productname LIKE T OR productid=46) AND(unitprice>16 00) productid productname supplierid unitprice 14 Tofu 6 23 25 29 ThoringerRostbratwurst 12 123 79 62 Tarteausucre 29 49 3 (3 row(s) affected) T 46 16 1 2 SELECT productid productname supplierid unitprice FROM products WHERE(productname LIKE T ) OR (productid=46 AND unitprice>16 00) productid productname qsuppl ierid unitprice 54 Tourtiere 25 7 45 62 Tarteausucre 29 49 3
SQL 27 23 Tunnbr'6d 9 9 19 Teatime Chocolate Biscuits 8 9 2 14 Tofu 6 23 25 29 Thoringer Rostbratwurst 12 123 79 (6 row(s) affected) 1.10.4 WHERE BETWEEN BETWEEN SQL Server BETWEEN AND (>=x AND<=y) (>x AND<y) NOT BETWEEN NOT 10 20 10 20 SELECT productname unitprice FROM products WHERE unitprice BETWEEN 10 AND 20 productname UnitpriCO Chai 18 Chang 19 AniseedSyrup 10 GenenShouyu Pavlova 15 5 SirRodney'sScones 10.. (29 row(s) affected) 17 45 1.10.5 WHERE IN IN
28 MCSE 2000 SQL 2000 IN OR SQL Server (NULL) = NULL NOT IN NOT suppliers SELECT companyname country FROM suppliers WHERE country IN( Japan Italy ) companyname Tokyo Traders Mayumi's Formaggi Fortini s.r.l. Pasta Buttini s.r.l. (4 row(s) affected) country Japan Japan Italy Italy suppliers IN OR 1 SELECT companyname country FROM suppliers WHERE country= Japan OR country= Italy companyname TOkyOTraders Mayumi's FormaggiFortini s Pasta Buttini s.r.l. country Japan Japan Italy Italy
SQL 29 ( 4row(s) affected) 1.10.6 (NULL) 0( ) ( ) IS NULL CREATETABLE IS NOT NULL suppliers fax SELECT companyname fax FROM suppliers WHERE fax IS NULL companyname ExoticLiquids New Orleans Cajun Delights Tokyo Traders fax NULL NULL NULL Cooperativa de Quesos LasCabras NULL. 1.11 1.11.1 ORDER BY (ASC) (DESC) ORDER BY SQL Server sp_helpsort
30 MCSE 2000 SQL 2000 ORDERBY SQL Server SQL Server ORDER BY ORDERBY ORDERBY text image ORDER BY products SELECT productid productname categoryid unitprice FROM products ORDER BY categoryid unitprice DESC productid productname categoryid Unitprice 38 C6tedeBlare 1 263 5000 43 1pohCoffee 1 46 0000 2 Chang 1 19 0000 1 Chai 1 18 0000 35 SteeleveStout 1 18 0000 39 Chartreuseverte 1 18 0000 76 Lakkalik60ri 1 18 0000 70 OutbackLager 1 15 0000 34 SasquatchAle 1 14 0000 67 LaughingLumbejackLager 1 14 0000 75 RhOnbr?iuKlosterbier 1 7 7500 24 GuaranfiFantAstica 1 4 5000 8 NorthwoodsCranberrySauce2 40 0000 61 Siropd'erable 2 28 5000 6 Grandma'sBoysenberrySpread 2 25 0000
SQL 31 (77 row(s) affected) ORDERBY SQLServer SELECT productid productname categoryid unitprice FROM products ORDER BY 3 4 DESC 1.11.2 DISTINCT DISTINCT DISTINCT SELECT ORDER BY DISTINCT DISTINCT ORDER BY ORDER BY suppliers SELECT DISTINCT country FROM suppliers ORDER BY country country Australia Brazil Canada
32 MCSE 2000 SQL 2000 Denm~k Finland France Germany ltaly Japan Netherlands Norway Singapore Spain Sweden UK USA (16row(s)affected) DISTINCT suppliers SELECT Country FROM Suppliers ORDER BY COuntry country Australia Australia Brazil Canada Canada Denmark Finland France France France Germany
SQL 33 Germany Germany ltaly ltaly Japan Japan Netherlands Norway Singpaore Spain (29row(s)affected) 1.11.3 AS SELECT column_namelexpression AS column_heading FROM table_name CREATE TABLE SQL Server 128 employees firstname lastname employeeid EmployeeID SELECT firstname AS First lastname AS Last employeeid AS Employee ID FROM employees First Last Employee ID Nancy Davolio 1
34 MCSE 2000 SQL 2000 Andrew Fuller 2 Janet Leverling 3 Margaret Peacock 4 Steven Buchanan 5 Michael Suyama 6 Robert King 7 Laura Callahan 8 Anne Dodsworth 9 (9row(s)affected) 1.11.4 Literals, SELECT column_name 'string literal' [, column_name 'string_literal'...] FROM table_name employees employeeid Identification " number SELECT firstname lastname Identification number employeeid FROM employees firstname lastname employeeid Nancy Davolio ldentificationnumber 1 Andrew Fuller ldentificationnumber 2 Janet Leverling ldentificationnumber 3 Margaret Peacock ldentificationnumber 4 Steven Buchanan ldentificationnumber 5 Michael Suyama ldentificationnumber 6 Robert King ldentificationnumber 7 Laura Callahan ldentificationnumber 8 Anne Dodsworth ldentificationnumber 9 (9row(s)affected) 1.12
SQL 35 SQLServer 1.12.1 (Ad Hoc) SELECT (join) (Form) 1.12.2 SQLServer, ad hoc batches (auto-parameterization) 1 SQL Server SQL Server $12 5 12 5 1 3 2 SELECT * FROM products WHERE unitprice = $12 5 SELECT * FROM products WHERE unitprice = 12 5 SELECT * FROM products WHERE unitprice = $12 5
36 MCSE 2000 SQL 2000 2 SQL Server USE library SELECT*FROM member WHERE member_no = 7890 SELECT*FROM member WHERE member_no = 1234 SELECT*FROM member WHERE member_no = 7890 1.13 SQLServer ( NO TBETWEEN NOT IN NOT NULL) LIKE LIKE ORDER BY SQL Server