Sql Syntax

SQL Summary BNF notations {} means REPEATED ELEMENT, [] means OPTIONAL, | means OR CREATE TABLE table-name ( column_name column_ type [attribute constraint] {, Fname VARCHAR(15) NOT NULL} [table constraint, PRIMARY KEY (Ssn), FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber), UNIQUE(Ssn)]); ALTER TABLE schema.table ADD COLUMN column_name column_type() ALTER TABLE schema.table DROP COLUMN column_name [CASCADE] DROP TABLE table_name [CASCADE] SELECT [DISTINCT] * | column_name | function DISTINCT removes double results FROM (table_name | joined_table) [WHERE condition] [GROUP BY column_name] [HAVING group_selection_conditions] [ORDER BY column_name [ASC | DESC]{, column_name [ASC|DESC]}] INSERT INTO table_name (column_name, column_name, column_name) VALUES (‘string’, number, variable) DELETE FROM table_name [WHERE selection_condition] UPDATE table_name SET column_name = value, column_name = value [WHERE selection_condition] JOINS SELECT column (s) FROM table1 INNER JOIN table2 ON table1.column =table2.column OUTER JOINS SELECT column (s) FROM table1 LEFT JOIN table2 table2 ON table1.column =table2.column SELECT column (s) FROM table1 RIGHT JOIN table2 table2 ON table1.column =table2.column

returns only matching values

returns all on table1 plus matching on

returns matching on table1 plus all on

SELECT column (s) FROM table1 FULL JOIN table2 where none matching ON table1.column =table2.column

returns all from both tables even

EXAMPLES SELECT Dname, COUNT(*) FROM Department, Employee WHERE Dnumber=Dno AND salary>40000 GROUP BY Dname HAVING COUNT(* ) > 5 ORDER BY Dname(ASC); SELECT SUM(salary), MAX(salary), MIN(salary), AVG(salary), COUNT(DISTINCT salary) WHERE (Salary BETWEEN 1000 AND 5000) AND Dno=5 WHERE Address LIKE ‘%Houston, Texas%’ WHERE column_name IS NULL RENAME (SELECT Fname AS Name, LName AS Surname, Salary AS YearlyIncome FROM (select * from EMPLOYEE) AS TEMP WHERE YearlyIncome >20 000) AS RICHONES SELECT tablenameold AS tablenamenew (newcolname1, newcolname2, newcolname3) PROJECT Comes automatically when you select a particular column/s That is – it returns a table with a reduced schema

Data Types SMALLINT – 16 bits integer INTEGER – 32 bits integer FLOAT(precision, scale) – floating point, a real number DOUBLE(precision, scale) – floating point, a real number DECIMAL(precision, scale) – formatted, a real number NUMERIC(precision, scale) – formatted, a real number CHAR(no of chars) – fixed length character string VARCHAR(max no of chars) – variable length character string DATE, TIME – date or time types YYYY-MM-DD and HH:MM:SS TIMESTAMP – Current date and time YYYY-MM-DD HH:MM:SS 123456 BOOLEAN – a true/false/unknown(in SQL) variable BIT(n), BIT VARYING(n) – Anything (user defined)