IM Prefi

Query

A specific request for data manipulation issued by the end-user or the application to the DBMS.

SQL

Stands for Structured Query Language, pronounced as S-Q-L or 'sequel'.

Exact Numeric Data Types

Data types including bigint, bit, decimal, int, money, numeric.

Approximate Numeric Data Types

Data types including float, real.

Date and Time Data Types

Data types including date, datetime, and time.

Character Strings Data Types

Data types including char, text, varchar.

Unicode Character Strings Data Types

Data types including nchar, ntext, nvarchar.

Binary Strings Data Types

Data types including binary, image, varbinary.

Other Data Types

Data types including cursor, sql_variant, table, uniqueidentifier, xml.

Arithmetic SQL Operators

Operators including +, -, *, /, %.

Comparison SQL Operators

Operators including =, >,

Compound SQL Operators

Operators including +=, -=, *=, /=, %=.

Logical SQL Operators

Operators including AND, OR, NOT, LIKE, IN, BETWEEN, EXISTS, ANY, ALL.

CREATE DATABASE

A command to create a new database.

DROP DATABASE

A command to delete an existing database.

CREATE TABLE

A command to create a new table in a database.

DROP TABLE

A command to delete an existing table in a database.

TRUNCATE TABLE

A command to delete only the table's data.

ALTER TABLE

A command that adds, deletes, or modifies columns in an existing table.

NOT NULL Constraint on CREATE TABLE

Ensures that a column cannot have a NULL value upon creating a table.

UNIQUE Constraint on CREATE TABLE

Ensures that all values in a column are different upon creating a table.

PRIMARY KEY Constraint on CREATE TABLE

Uniquely identifies each row in a table.

FOREIGN KEY on CREATE TABLE

Uniquely identifies a row in another table.

CHECK Constraint on CREATE TABLE

Ensures that all values in a column satisfy a specific condition upon creating a table.

DEFAULT Constraint on CREATE TABLE

Sets a default value for a column when there is no value specified.

INSERT INTO

A command that adds new rows/records to a table.

SELECT

A command that retrieves values of all rows or a subset of rows in a table.

DISTINCT Operator

Used with SELECT to retrieve unique values from columns in a table.

WHERE Clause

Used with SELECT to filter the rows of data based on provided criteria.

IS NULL Operator

Used with SELECT to determine whether a field is empty or not.

LIKE Operator

Used with WHERE to determine whether a value matches a given string pattern.

IN Operator

Used with WHERE to check whether a value matches any value within a given list.

BETWEEN Operator

Used with WHERE to check whether a value is within a range.

ORDER BY Clause

Used with SELECT to sort retrieved values in ascending or descending order.

UPDATE Command

Modifies existing records in a table.

DELETE Command

Removes existing records in a table.

CREATE TABLE Example

CREATE TABLE Students (StudentID varchar(11), LastName varchar(99), FirstName varchar(99), Section varchar(5));

DROP TABLE Example

DROP TABLE Students;

ALTER TABLE Add Example

ALTER TABLE Students ADD MiddleName varchar(99);

ALTER TABLE Drop Example

ALTER TABLE Students DROP COLUMN Section;

ALTER TABLE Modify Example

ALTER TABLE Students ALTER COLUMN MiddleName nvarchar(99);

UNIQUE Constraint Example (ALTER TABLE)

ALTER TABLE Students ADD UNIQUE(StudentID);

PRIMARY KEY Example (ALTER TABLE)

ALTER TABLE Students ADD PRIMARY KEY (StudentID);

FOREIGN KEY Example

CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY, TableNumber int NOT NULL, CustomerID int FOREIGN KEY REFERENCES Customers (CustomerID));

Check Constraint Example

CREATE TABLE Students (StudentID varchar(11) NOT NULL, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Age int CHECK (Age>=15));

Default Value Example

CREATE TABLE Students (StudentID varchar(11) NOT NULL, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Section varchar(5) DEFAULT 'Not yet enrolled');

INSERT INTO Example

INSERT INTO Students (LastName, Section) VALUES ('Reyes', 'IT102');

SELECT Example

SELECT LastName, Section FROM Students;

DISTINCT Usage Example

SELECT DISTINCT Section FROM Students;

WHERE Clause Example

SELECT * FROM Students WHERE Section = 'IT101';

IS NULL Usage Example

SELECT LastName, Section FROM Students WHERE Section IS NULL;

LIKE Usage Example

SELECT * FROM Students WHERE LastName LIKE '_b%';

IN Usage Example

SELECT * FROM Students WHERE Section IN ('IT101', 'IT102', 'IT103');

BETWEEN Usage Example

SELECT * FROM Students WHERE Age BETWEEN 13 AND 15;

ORDER BY Example

SELECT * FROM STUDENTS ORDER BY LastName;

UPDATE Example

UPDATE Students SET Section = 'IT202', Status = 'Irregular' WHERE StudentID = '2018-100013';

DELETE Example

DELETE FROM Students WHERE StudentID = '2018- 100013';

INSERT INTO syntax

INSERT INTO table_name (columns) VALUES (values);

INSERT INTO (all columns) syntax

INSERT INTO table_name VALUES (values);

SELECT syntax

SELECT columns FROM table_name;

SELECT all columns syntax

SELECT * FROM table_name;

SELECT DISTINCT syntax

SELECT DISTINCT columns FROM table_name;

SELECT with WHERE syntax

SELECT columns FROM table_name WHERE condition;

SELECT with IS NULL syntax

SELECT columns FROM table_name WHERE column IS NULL;

SELECT with LIKE syntax

SELECT columns FROM table_name WHERE column LIKE pattern;

SELECT with IN syntax

SELECT columns FROM table_name WHERE column IN (values);

SELECT with BETWEEN syntax

SELECT columns FROM table_name WHERE column BETWEEN value1 AND value2;

SELECT with ORDER BY syntax

SELECT columns FROM table_name ORDER BY columns;

SELECT with ORDER BY DESC syntax

SELECT * FROM table_name ORDER BY columns DESC;

UPDATE syntax

UPDATE table_name SET column1 = value1, … WHERE condition;

DELETE syntax

DELETE FROM table_name WHERE condition;

DELETE all records syntax

DELETE FROM table_name;

CREATE DATABASE syntax

CREATE DATABASE database_name;

DROP DATABASE syntax

DROP DATABASE database_name;

CREATE TABLE syntax

CREATE TABLE table_name (column1 datatype, …);

DROP TABLE syntax

DROP TABLE table_name;

TRUNCATE TABLE syntax

TRUNCATE TABLE table_name;

ALTER TABLE ADD syntax

ALTER TABLE table_name ADD column datatype;

ALTER TABLE DROP COLUMN syntax

ALTER TABLE table_name DROP COLUMN column;

ALTER TABLE ALTER COLUMN syntax

ALTER TABLE table_name ALTER COLUMN column datatype;

NOT NULL on CREATE TABLE syntax

CREATE TABLE Students (column datatype NOT NULL, …);

NOT NULL on ALTER TABLE syntax

ALTER TABLE Students ALTER COLUMN column datatype NOT NULL;

UNIQUE on CREATE TABLE syntax

CREATE TABLE Students (column datatype UNIQUE, …);

UNIQUE on ALTER TABLE syntax

ALTER TABLE table_name ADD UNIQUE (column);

PRIMARY KEY on CREATE TABLE syntax

CREATE TABLE Students (column datatype PRIMARY KEY, …);

PRIMARY KEY on ALTER TABLE syntax

ALTER TABLE table_name ADD PRIMARY KEY (column);

FOREIGN KEY on CREATE TABLE syntax

CREATE TABLE Orders (column datatype FOREIGN KEY REFERENCES table2name(table2column));

FOREIGN KEY on ALTER TABLE syntax

ALTER TABLE table1name ADD FOREIGN KEY (table1column) REFERENCES table2name(table2column);

CHECK on CREATE TABLE syntax

CREATE TABLE Students (column datatype CHECK (condition), …);

CHECK on ALTER TABLE syntax

ALTER TABLE table_name ADD CHECK (condition);

DEFAULT on CREATE TABLE syntax

CREATE TABLE Students (column datatype DEFAULT 'value', …);

DEFAULT on ALTER TABLE syntax

ALTER TABLE tablename ADD CONSTRAINT constraint