compsci-notes-spring-2024/notes/database-prog/syntax.md

SQL Syntax

Keywords

|: Use for designating different things that can be used as arguments for the keywords ( ): Optional keywords [ ]: Used for values, like integers, or column or table names.

Keyword	Explanation	Example
USE	Specify the database to be used	USE WideWorldImporters
SELECT {ColumnID | *}	selects the column to return (or can use wildcard *)	SELECT ProductID FROM Products
FROM	specify the thing to be selected from
GO {count}	Runs everything before it count times minus 1	GO 5 (runs everything before it 4 more times)
TOP {n} (PERCENT - optional)	Returns the top n (or n%) results - must be an integer . Order is undefined, but in most implementations is listed in the order they were inserted - NOT guaranteed behavior, just how most implementations use it.	SELECT TOP 10 PERCENT VendorName FROM Vendors
ORDER BY {column1, column2 (1 required, can use infinitely many)}	Orders by some column (alphanumerically sorted, letters first); TOP is useless without this	SELECT TOP 50 InvoiceID, VendorID FROM Invoices ORDER BY VendorID
DISTINCT	Goes before the column names (and before TOP in MS SQL server, at least); removes rows with duplicate values	SELECT DISTINCT VendorName FROM Invoices

CASE/WHEN/THEN/ELSE/END

Other syntax

• Brackets [ ]: Delineates that something is a table, usually not needed but allows for stuff like using keywords as table names.

Other notes

• SELECT * FROM Table: Shouldn't be used normally, only in learning or if you're not sure what you need yet.
  • Bad because: it's requests every column of data in the table, and that's rarely needed, wasting time and resources.
  • Most database systems will just skip all qury optimizations if you do this, wasting even more time and resources.
  • Column ordering is undefined - the columns could be returned in any random order.
• Should usually do one keyword per line, except for simple stuff like SELECT ColumnID FROM Table
• Column aliases: Useful for:
  • Multi-table connections and for "renaming" columns
  • Making it more convenient to use (e.g. Person -> Name as an alias of Person -> PersonName)
  • To name an unnamed column (e.g. SELECT LEFT(VendorName, 10) VendorShortName FROM Vendors)

No

Don't:

• Put spaces in a table's name (why would you???)
• Use SELECT * FROM Table