SQL

Objectives

• learn how to use SELECT, FROM, and WHERE to retrieve and filter data from a relational database.
• apply the ORDER BY clause to sort results in ascending (ASC) or descending (DESC) order based on specified columns.
• gain an understanding of how to use SQL to query data from more than one table using joins or table references.
• use the INSERT INTO command to add new data into specific tables of a relational database.
• use the UPDATE and DELETE commands to modify or remove records based on specified conditions within the database.

SQL (Structured Query Language) is a programming language used to manage and interact with relational databases. You will primarily use it to retrieve, insert, update, and delete data. Here’s a breakdown of key SQL commands that you need to know:

The SELECT Statement

The SELECT statement is used to retrieve data from a database.

Basic Syntax:

SELECT column1, column2, ... 
FROM table_name;

• SELECT: Specifies the columns you want to retrieve.
• FROM: Indicates the table from which to fetch the data.

Example:

SELECT StudentName, Age 
FROM Students;

This retrieves the StudentName and Age columns from the Students table.

Using WHERE to Filter Data

The WHERE clause is used to filter records based on a condition.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• WHERE: Specifies the condition that must be met for a record to be included in the results.

Example:

SELECT StudentName, Age 
FROM Students
WHERE Age > 15;

This retrieves the StudentName and Age of students who are older than 15.

Sorting Results with ORDER BY

The ORDER BY clause is used to sort the result set in ascending or descending order.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC];

• ASC: Sorts the data in ascending order (default).
• DESC: Sorts the data in descending order.

Example:

SELECT StudentName, Age 
FROM Students
ORDER BY Age DESC;

This retrieves the StudentName and Age, and sorts the result by Age in descending order (from oldest to youngest).

Using SQL with Two Tables

You can extract data from more than one table by using JOIN or simply referencing multiple tables.

Example with Two Tables:

SELECT Students.StudentName, Classes.ClassName 
FROM Students, Enrollment, Classes
WHERE Students.StudentID = Enrollment.StudentID 
AND Classes.ClassID = Enrollment.ClassID;

This retrieves the StudentName and ClassName by combining the Students, Enrollment, and Classes tables using relationships between the tables.

Inserting Data into a Relational Database

You can add new records to a table using the INSERT INTO statement.

Basic Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

• INSERT INTO: Specifies the table into which the data will be inserted.
• VALUES: Provides the actual data for the columns.

Example:

INSERT INTO Students (StudentID, StudentName, Age)
VALUES (4, 'Emily Green', 17);

This inserts a new student record with ID 4, name Emily Green, and age 17 into the Students table.

Editing and Deleting Data in a Database

You can modify or delete existing records in a database using the UPDATE and DELETE commands.

Updating Data with the UPDATE Statement

The UPDATE statement is used to change existing data in a table.

Basic Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• SET: Specifies the columns and their new values.
• WHERE: Defines the condition to specify which rows should be updated.

Example:

UPDATE Students
SET Age = 18
WHERE StudentName = 'John Doe';

This updates the Age of the student named 'John Doe' to 18.

Deleting Data with the DELETE Statement

The DELETE statement is used to remove records from a table.

Basic Syntax:

DELETE FROM table_name
WHERE condition;

• DELETE FROM: Specifies the table from which to delete records.
• WHERE: Sets the condition that defines which rows to delete.

Example:

DELETE FROM Students
WHERE StudentID = 4;

This deletes the student with StudentID of 4 from the Students table.

Summary of SQL Commands

Command	Purpose	Example
SELECT	Retrieve data from a table.	SELECT StudentName FROM Students;
FROM	Specifies the table from which data is retrieved.	SELECT * FROM Students;
WHERE	Filters records based on a condition.	SELECT * FROM Students WHERE Age = 16;
ORDER BY	Sorts data in ascending or descending order.	SELECT * FROM Students ORDER BY Age DESC;
INSERT INTO	Inserts new data into a table.	INSERT INTO Students (StudentID, StudentName, Age) VALUES (1, 'John Doe', 16);
UPDATE	Updates existing data in a table.	UPDATE Students SET Age = 17 WHERE StudentID = 1;
DELETE	Deletes data from a table.	DELETE FROM Students WHERE StudentID = 1;

These commands allow you to manage and interact with a relational database effectively, handling everything from simple data retrieval to complex data manipulation.

SQL Crib Sheet

Questions

1. What is the purpose of the SQL SELECT statement?

To insert new data into a table.

To retrieve data from a table.

To delete data from a table.

To update data in a table.

The SELECT statement is used to retrieve data from a table in a relational database.

Submit

2. Which SQL clause is used to filter records based on a condition?

ORDER BY

FROM

WHERE

SELECT

The WHERE clause is used to filter data based on a specified condition.

Submit

3. How would you sort results in descending order by a column called 'Age'?

ORDER BY Age ASC;

ORDER BY Age;

ORDER BY Age DESC;

ORDER BY Age LIMIT 1;

The ORDER BY Age DESC sorts the results in descending order by the 'Age' column.

Submit

4. In the SQL statement SELECT StudentName, Age FROM Students;, what does the FROM clause do?

It retrieves data from the database.

It specifies the table from which to retrieve data.

It filters the data.

It updates the table with new data.

The FROM clause specifies the table, in this case 'Students', from which to retrieve data.

Submit

5. Which SQL command is used to insert new data into a table?

SELECT

INSERT INTO

UPDATE

DELETE

The INSERT INTO statement is used to add new records to a table.

Submit

6. Which of the following statements will delete a record from the 'Students' table with StudentID = 4?

DELETE FROM Students WHERE StudentID = 4;

DROP Students WHERE StudentID = 4;

REMOVE FROM Students WHERE StudentID = 4;

TRUNCATE Students WHERE StudentID = 4;

The DELETE FROM statement is used to remove records, and the condition filters the deletion to StudentID 4.

Submit

7. What is the purpose of the UPDATE SQL statement?

To retrieve data from the database.

To delete records from a table.

To update existing data in a table.

To insert new data into a table.

The UPDATE statement modifies existing records in a table based on a condition.

Submit

8. Which SQL statement retrieves the names of students older than 15?

SELECT StudentName FROM Students WHERE Age = 15;

SELECT StudentName FROM Students WHERE Age > 15;

SELECT StudentName FROM Students WHERE Age < 15;

SELECT StudentName FROM Students ORDER BY Age DESC;

The WHERE clause with Age > 15 filters the data to return only students older than 15.

Submit

9. What is the purpose of a JOIN in SQL?

To combine records from two or more tables.

To insert data into multiple tables at once.

To delete data from multiple tables.

To update records across tables.

A JOIN is used to combine rows from two or more tables based on a related column.

Submit

10. Which SQL statement adds a new student with ID 4, name 'Emily Green', and age 17?

INSERT INTO Students (StudentID, StudentName, Age) VALUES (4, 'Emily Green', 17);

INSERT Student INTO Students (4, 'Emily Green', 17);

ADD TO Students VALUES (4, 'Emily Green', 17);

INSERT (4, 'Emily Green', 17) INTO Students;

The correct syntax for inserting a new record uses INSERT INTO followed by the column names and VALUES for the data.

Submit