IM Prefi

A query - is 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 - bigint, bit, decimal, int, money,numeric

Approximate numeric - float, real

Date and time - date, datetime, time

Character strings - char, text, varchar

Unicode character strings - nchar, ntext, nvarchar

Binary strings - binary, image, varbinary

Other data types - cursor, sql_variant, table, uniqueidentifier, xml

SQL Operators:

Arithmetic - +, -, *, /, %

Comparison- =, >, <, >=, <=, <>

Compound- +=, -=, *=, /=, %=

Logical - AND, OR, NOT, LIKE, IN, BETWEEN, EXISTS, ANY, ALL

SQL Data Definition Commands

• CREATE DATABASE – creates a new database

o Syntax: CREATE DATABASE database_name;

o Example: CREATE DATABASE myDB;

• DROP DATABASE – deletes an existing database

o Syntax: DROP DATABASE database_name;

o Example: DROP DATABASE myDB;

• CREATE TABLE – creates a new table in a database

o Syntax: CREATE TABLE table_name (column1 datatype, ...);

o Example: CREATE TABLE Students (StudentID varchar(11), LastNamevarchar(99), FirstNamevarchar(99), Section varchar(5));

• DROP TABLE – deletes an existing table in a database

o Syntax: DROP TABLE table_name;

o Example: DROP TABLE Students;

o To delete only the table's data:

▪ Syntax: TRUNCATE TABLE table_name;

▪ Example: TRUNCATE TABLE Students;

• ALTER TABLE – Adds, deletes, or modifies columns in an existing table

o Syntax to add: ALTER TABLE table_name ADD column datatype;

o Example: ALTER TABLE Students ADD MiddleName varchar(99);

o Syntax to delete: ALTER TABLE table_name

DROP COLUMN column;

o Example: ALTER TABLE Students DROP COLUMN Section;

o Syntax to modify: ALTER TABLE table_name ALTER COLUMN column datatype;

o Example: ALTER TABLE Students ALTER COLUMN MiddleName nvarchar(99);

SQL Constraints

• NOT NULL on CREATE TABLE – ensures that a column cannot have a NULL value upon creating a table

o Example: CREATE TABLE Students (StudentID varchar(11) NOT NULL, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Section varchar(5));

NOT NULL on ALTER TABLE – ensures that a column in an existing table cannot have a NULL value

o Example: ALTER TABLE Students ALTER COLUMN Section varchar(5) NOT NULL;

• UNIQUE on CREATE TABLE – ensures that all values in a column are different upon creating a table

o Example: CREATE TABLE Students (StudentID varchar(11) NOT NULL UNIQUE, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Section varchar(5));

• UNIQUE on ALTER TABLE – creates a UNIQUE constraint on a column of an existing table

o Syntax: ALTER TABLE table_name ADD UNIQUE (column);

o Example: ALTER TABLE Students ADD UNIQUE(StudentID);

• PRIMARY KEY on CREATE TABLE – uniquely identifies each rowin a table

o Example: CREATE TABLE Students (StudentID varchar(11) NOT NULL PRIMARY KEY, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Section varchar(5));

• PRIMARY KEY on ALTER TABLE – creates a PRIMARY KEY constraint on a column of an existing table

o Syntax: ALTER TABLE table_name ADD PRIMARY KEY (column);

o Example: ALTER TABLE Students ADD PRIMARY KEY (StudentID);

• FOREIGN KEY on CREATE TABLE – uniquely identifies a row inanother table

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

• FOREIGN KEY on ALTER TABLE – creates a FOREIGN KEY constraint on a column of an existing table

o Syntax: ALTER TABLE table1_name ADD FOREIGN KEY (table1_column) REFERENCES table2_name (table2_column);

o Example: ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID);

• CHECK on CREATE TABLE – ensures that all values in a column satisfy a specific condition upon creating a table

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

• CHECK on ALTER TABLE – ensures that all values in a column of an existing table satisfy a specific condition

o Syntax: ALTER TABLE table_name ADD CHECK (condition);

o Example: ALTER TABLE Students ADD CHECK (Age>=15);

• DEFAULT on CREATE TABLE – sets a default value for a column when there is no value specified

o 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');

• DEFAULT on ALTER TABLE – sets a default value for a column of an existing table when there is no value specified

o Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT 'value' FOR column;

o Example: ALTER TABLE Students ADD CONSTRAINT df_section DEFAULT 'Not yet enrolled' FOR Section;

SQL Data Manipulation Commands

• The basic data manipulation commands are - INSERT, SELECT UPDATE, and DELETE.

• INSERT INTO – adds new rows/records to a table

o Syntax: INSERT INTO table_name (columns) VALUES (values);

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

o To add new records to all the columns of a table

▪ Syntax: INSERT INTO table_name VALUES (values);

▪ Example: INSERT INTO Students VALUES ('Reyes', 'IT102');

• SELECT – retrieves values of all rows or a subset of rows in a table

o Syntax: SELECT columns FROM table_name;

o Example: SELECT LastName, Section FROM Students;

o To select all columns:

▪ Syntax: SELECT * FROM table_name;

▪ Example: SELECT * FROM Students;

• DISTINCT – an operator used with SELECT to retrieve unique values from columns in a table

o Syntax: SELECT DISTINCT columns FROM table_name;

o Example: SELECT DISTINCT Section FROM Students;

• WHERE – an option used with SELECT to filter the rows of data based on provided criteria

o Syntax: SELECT columns FROM table_name WHERE condition;

o Example: SELECT * FROM Students WHERE Section = 'IT101';

o To select numeric fields, do not enclose in quotation marks.

Example: SELECT * FROM Students WHERE Age >= 18;

• IS NULL – an operator used with SELECT to determine whether a field is empty or not

o Syntax: SELECT columns FROM table_name WHERE column IS NULL;

o Example: SELECT LastName, Section FROM Students WHERE Section IS NULL;

• LIKE – an operator used with WHERE to determine whether a value matches a given string pattern

o Syntax: SELECT columns FROM table_name WHERE column LIKE pattern;

o Wildcards: % represents zero, one, or multiple characters while _ represents a single character

o Example: SELECT * FROM Students WHERE LastName LIKE '_b%';

o Meaning: All students with last names that have 'b' in the second position.

• IN – an operator used with WHERE to check whether a value matches any value within a given list

o Syntax: SELECT columns FROM table_name WHERE column IN (values);

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

• BETWEEN – an operator used with WHERE to check whether a value is within a range

o Syntax: SELECT columns FROM table_name WHERE column BETWEEN value1 AND value2;

o Example: SELECT * FROM Students WHERE Age BETWEEN 13 AND 15;

• ORDER BY – An option used with SELECT to sort retrieved values in ascending or descending order

o Syntax: SELECT columns FROM table_name ORDER BY columns;

o Example: SELECT * FROM STUDENTS ORDER BY LastName

o To sort values in descending order:

SELECT * FROM table_name ORDER BY columns DESC;

• UPDATE – modifies existing records in a table

o Syntax: UPDATE table_name SET column1 = value1, ... WHERE condition;

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

• DELETE – removes existing records in a table

o Syntax: DELETE FROM table_name WHERE condition;

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

o To delete all records: DELETE FROM table_name;