SQL : Basic SELECT Queries

Objectives:

• Understand the SELECT statement syntax
• Retrieve all columns and specific columns from a table
• Use aliases to simplify column names

1. Introduction to SELECT Statement (10 minutes)

Syntax Overview:

• The basic syntax of the SELECT statement is:

  SELECT column1, column2, ...
  FROM table_name;

• Example:

  SELECT * FROM TestTable;

• * retrieves all columns from the table.

2. Retrieving All Columns (5 minutes)

• Using * to Select All Columns:

  SELECT * FROM TestTable;

• Exercise:
• Write a query to select all columns from the TestTable.

3. Retrieving Specific Columns (10 minutes)

Syntax for Selecting Specific Columns:

• Example: Select ID and Name from TestTable.

  SELECT ID, Name FROM TestTable;

Exercise:

• Write SQL queries to retrieve:
• Only ID and DateOfBirth from TestTable.
• Only Name and DateOfBirth from TestTable.

4. Using Aliases to Simplify Column Names (10 minutes)

Syntax for Aliases:

• Use the AS keyword to rename columns:

  SELECT ID AS StudentID, Name AS StudentName FROM TestTable;

• Example:

  SELECT ID AS StudentID, Name AS StudentName, DateOfBirth AS DOB FROM TestTable;

Exercise:

• Write SQL queries to:
• Select ID as StudentID, Name as StudentName, and DateOfBirth as DOB from TestTable.
• Use aliases for the columns to make the output clearer.

5. Practical Exercise: Basic SELECT Queries (10 minutes)

• Task:
• Create a new table named Students with columns StudentID, FirstName, LastName, and EnrollmentDate. CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), EnrollmentDate DATE );
• Insert sample data into the Students table: INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate) VALUES (1, 'John', 'Doe', '2024-01-15'), (2, 'Jane', 'Smith', '2024-01-16'), (3, 'Emily', 'Johnson', '2024-01-17');
• Exercises:

1. Retrieve all columns from the Students table.
2. Select only FirstName and LastName from the Students table.
3. Use aliases to display StudentID as ID, FirstName as First, and LastName as Last.

Exercises

1. Basic SELECT Queries:

• Exercise: Write SQL queries to:
  • Select all columns from TestTable.
  • Select specific columns: ID, Name, DateOfBirth.

1. Using Aliases:

• Exercise: Select ID as StudentID, Name as StudentName, and DateOfBirth as DOB from TestTable.

1. Creating and Populating Students Table:

• Exercise:
  • Create the Students table with the specified columns.
  • Insert sample data into the Students table.

1. Sample Queries for Students Table:

• Exercise: Execute the following queries:
  1. Retrieve all columns from the Students table.
  2. Select FirstName and LastName from the Students table.
  3. Use aliases to display StudentID as ID, FirstName as First, and LastName as Last.

Homework:

• Practice Questions:
• Write SQL queries to:
  • Select all columns from Students.
  • Retrieve FirstName and EnrollmentDate from Students.
  • Use aliases to format column names for better readability.