Learning of SQL Day 7

 

Day 7: SELECT Statement Basics

Introduction:

• The SELECT statement is the most commonly used SQL statement. It retrieves data from one or more tables.

Key Concepts:

• SELECT Statement: Used to fetch data.

• SELECT * FROM Table: Fetches all columns.

• SELECT Column1, Column2 FROM Table: Fetches specific columns.

• Aliasing: Renaming columns for readability.

• DISTINCT Keyword: Removes duplicate records.

SQL Commands and Examples:

1. Selecting All Columns:

   • Retrieve all columns from a table.

sql

-- Select all columns from the Students table
SELECT * FROM Students;

2. Selecting Specific Columns:

   • Retrieve specific columns from a table.

sql

-- Select FirstName and Age columns from the Students table
SELECT FirstName, Age FROM Students;

3. Using Aliases:

   • Renaming columns to make output more readable.

sql

-- Select columns with aliases
SELECT FirstName AS 'First Name', Age AS 'Student Age' FROM Students;

4. Using DISTINCT:

   • Remove duplicate records from the result set.

sql

-- Select unique ages from the Students table
SELECT DISTINCT Age FROM Students;

Practice Exercise:

1. Create a table named Courses with columns CourseID (INT), CourseName (VARCHAR), and Credits (INT).

2. Insert a few records into the Courses table.

3. Write a query to select all columns from the Courses table.

4. Write a query to select only the CourseName and Credits columns.

5. Write a query to select the CourseName column and alias it as Course Title.

6. Write a query to select distinct Credits values.

sql

-- Create the Courses table
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(255),
    Credits INT
);

-- Insert records into the Courses table
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES 
(201, 'Mathematics', 4),
(202, 'Physics', 3),
(203, 'Chemistry', 3),
(204, 'Biology', 4);

-- Select all columns
SELECT * FROM Courses;

-- Select specific columns
SELECT CourseName, Credits FROM Courses;

-- Select columns with aliases
SELECT CourseName AS 'Course Title', Credits AS 'Credit Hours' FROM Courses;

-- Select distinct credits
SELECT DISTINCT Credits FROM Courses;

Mastering the SELECT statement is fundamental for querying databases effectively.