Common SQL Commands & SQL Select Statements

SELECT

Retrieves specific data from a database

UPDATE

Modifies existing data w/in a table.

DELETES

Removes specific records from database

INSERT INTO

Adds new rows into a table

CREATE DATABASE

Establishes a new database from scratch

ALTER DATABASE

Adjusts database' settings or attributes (fields/columns)

CREATE TABLE

Defines a new table w/ columns & data types

ALTER TABLE

• Changes the structure of a table

• For e.g:

• Adding/removing a column

• Changing a column’s data type

• Renaming a table or column

• Adding/dropping constraints

- Such as adding, renaming, or removing columns

DROP TABLE

- Permanently deletes a table & all of its data (including its structure)

- Cannot be undone (no rollback allowed)

CREATE INDEX

• Improves speed of data retrieval operations on a table

  • does this by creating pointers to data based on 1 or more columns

• Doesn’t affect the data itself—it’s purely a performance booster.

JOIN

Combines records from two or more tables based on a common column.

TRUNCATE TABLE

Removes all records (data) from a table, but keeps its structure intact.

DISTINCT

Retrieves unique values from a column.

ORDER BY

Sorts query results in ascending or descending order.

GROUP BY

Aggregates data based on one column.

HAVING

Filters grouped results (*works with GROUP BY command)

LIMIT

Restricts the number of rows that are returned.

CASE

Adds conditional logic to an SQL query.

UNION

Combines results from multiple SELECT queries.

EXISTS

checks if a subquery returns any rows

BETWEEN

Filters results w/in a range

GRANT

Gives a privilege to a user.

REVOKE

Takes back privileges granted to a user.

COMMIT

Saves all changes made during current transaction

ROLLBACK

Reverts (undos) changes made during current transaction

SAVEPOINT

Sets a point w/in a transaction to which changes can be rolled back (allowing partial rollbacks)

DELETE v.s. TRUNCATE TABLE v.s. DROP TABLE

DELETE

• Deletes specific rows from table

• Keeps table structure

• Rollback is always possible

Use When: You want to remove certain rows w/o affecting table itself.

DELETE FROM employees WHERE department = 'HR'; 

-- This only deletes rows where department = 'HR' 

TRUNCATE TABLE

• Deletes all records form a table

• Keeps table structure

• Rollback is not always possible

Use When: You want to remove all rows from a table quickly

TRUNCATE TABLE employees; 

-- Deletes all data from the employees table, but structure remains

DROP TABLE

• Deletes table AND deletes its structure, permanently

• No rollback (irreversible)

Use When: You no longer need a table at all

DROP TABLE employees; 

-- This permanently deletes the employees table, and cannot be undone

What is a ROLLBACK command?

• A command used to undo changes that have been made to a database w/in a transaction, but have not yet been committed

• Essentially — it restores database to previous state before changes were executed

In a database, what do each of the following represent:

(a) Rows

(b) Columns

(c) Cells

(a) Rows = Records

(b) Columns = Fields/Attributes (define type of data stored in each record)

(c) Cells = Individual data points

Is SQL generally case-sensitive or case-insensitive?

Describe how case rules apply to keywords/commands, table/column names, aliases/variables, and string values.

SQL is generally case-insensitive, but it is encouraged to follow certain rules when it comes to case:

Keywords & Commands — written in ALL CAPS

• E.g. SELECT, WHERE, FROM

Tables & Column Names — written in camelCase or Pascal_Case

• E.g: employees, orderDetails, customer_name

Aliases & Variables — written in camelCase or Pascal_Case

String Values — case doesn’t matter

T or F: Case doesn’t matter for string value comparisons.

True

Data Types

• Numeric Types

• Character & String Types

• Date & Time Types

• Binary Types

• Boolean Type

Numeric Types

• INT — Integer (whole number)

• DECIMAL (p,s) — Fixed precision numbers

• FLOAT — Approximate floating-point numbers

Character & String Types

• CHAR(n) — Fixed-length string

• VARCHAR(n) — Variable-length string

• TEXT — Large text storage

Date & Time Types

• DATE — Stores date only

• TIME — Stores time only

• DATETIME — Stores both date & time

Binary Types

• BINARY(n) — Fixed-length binary data

• VARBINARY(n) — Variable-length binary data

• IMAGE — Large binary storage (deprecated)

Boolean Type

• BIT — Stores 0 (false) or 1 (true)

What is a transaction?

• Definition

• Significance

Definition

Group of SQL statements that are executed as a single unit of work.

Transaction

Transactions ensure data integrity & consistency by allowing for the rollback of changes if any part of the transaction fails.

SQL Code — To display all data (records) in a table.

SELECT * 
  FROM tableName; 

-- this displays all fields in table called "tableName"

‘ * ‘ means all fields

SQL Code — To display specific records in a table.

SELECT * 
  FROM tableName; 
  WHERE attributeName = 'someValue'; 

-- this displays records from table called "tableName" where attributeName = someValue

SQL Code

To get around issue of mixed case in a string

SELECT * 
  FROM 
WHERE UPPER(attributeName) = 'BLAH'

-- The data in attributeName is converted to upper case and compare to "BLAH"

SQL Code

To display specific records in a table where a certain field is null.

SELECT * 
  FROM tableName
  WHERE tableAttribute IS NULL; 

SELECT *
  FROM customer
 WHERE custcity = null;

What is wrong with the code?

Line 3 has incorrect syntax

To display the record(s) in the custcity field where the value is null…

 WHERE custcity IS null; -- You have to use keyword 'is'

SQL Code

To specify multiple conditions…

use AND or OR keywords

E.g.

SELECT *
  FROM customer
 WHERE custcity='BRAMPTON'
   AND srepno IS NOT NULL;

--Sample Code 1
SELECT * FROM customer;
SELECT * FROM salesorder;

--Sample Code 2
SELECT * FROM salesorder, customer;

What is the different b/w the sample codes?

The key difference between Example 1 and Example 2 is how they retrieve data from multiple tables.

--Sample Code 1
SELECT * FROM customer;
SELECT * FROM salesorder;

• Each statement runs independently

• Returns all records from customer table first, then all records from salesorder table second

• Use this method for retrieving data instead of sample code 2

--Sample Code 2
SELECT * FROM salesorder, customer;

• This query performs a cross join

• It combines every row from salesorder table w/ every row from the customer table

• If salesorder has 10 rows & customer table hs 5 rows, the result (output) will be 10 × 5 = 50 rows

In SQL, string values are enclosed in __________________.

In SQL, string values are enclosed in single quotes.

How do you include a quote inside of a string?

Use double quote marks

CAST() / CONVERT()

Converts value from one data type to another.

Example:

SELECT CAST(123.45 AS INT);  -- returns 123

*CONVERT() allows re-formatting of date/time

Example:

SELECT CONVERT(VARCHAR, GETDATE(), 101) AS FormattedDate;

• GETDATE() returns the current date and time.

• CONVERT() changes it to a VARCHAR string.

• The style code 101 formats it as mm/dd/yyyy—for example, 06/23/2025.

Demonstrate:

How to add a taxAmount column to a preexisting table called finances.

ALTER TABLE finances
ADD COLUMN taxAmount 

Aggregates

Aggregates refer to functions that perform calculations on a set of values and return a single result.

DDL

• Stands for…

• Purpose?

DDL = Data Definition Language

Purpose: Defines structure/schema of database tables.

DML

• Stands for…

• Purpose?

DML = Data Manipulation Language

Purpose = Manages data within existing tables.

DCL

• Stands for…

• Purpose?

DCL = Data Command Language

Purpose:

Which 4 core commands belongs to DDL?

(HINT: Cool Architects Design Towers)

CREATE

ALTER

DROP

TRUNCATE

Which 4 core commands belongs to DML?

(HINT: Insane Urologists Demand Salaries)

INSERT INTO

UPDATE

DELETE FROM

SELECT

T or F: Strings in SQL can be enclosed within either single quotes or double quotes.

False. Strings in SQL are always enclosed in single quotes.

In SQL Server (T-SQL), double quotes are used only when quoting identifiers, such as column or table names if they contain spaces or reserved keywords — and even then, using [square brackets] is more common:

sql

Copy code

SELECT "first_name" FROM Customers; -- Not common SELECT [first name] FROM Customers; -- Better practice