PART-2 : Mastering Data Retrieval with SELECT Statements

Defining the Learners, Courses Table

• COURSE TABLE

-- Create a table by the name of courses having field named "CourseID", "CourseName", "Course_duration_months", "Course_Fee"

CREATE TABLE Course(
CourseID INT AUTO_INCREMENT,
CourseName varchar(50) NOT NULL,
CourseDuration_Months INT NOT NULL,
CourseFee INT NOT NULL,
PRIMARY KEY(CourseiD))

-- Read the structure of the Course table, listing column names, data types, and other attributes
DESC Course

--Insert the course details inside the table named courses 
-- SQL Bootcamp, 1, 999
-- DSA Interview Prep, 3, 4999
-- Fullstack Interview Prep, 2, 6999

INSERT INTO Course(CourseName, CourseDuration_Months,CourseFee) Values("SQL Bootcamp", 1, 999);
INSERT INTO Course(CourseName, CourseDuration_Months, CourseFee) Values("DSA Interview Prep", 3, 4999);
INSERT INTO Course(CourseName, CourseDuration_Months, CourseFee) Values("Fullstack Interview Prep", 2, 6999); |


-- Read all records from the Course table
SELECT * FROM Course

• LEARNERS TABLE

-- Creating the Courses table with field names "CourseID", "CourseName", "Course_duration_months", "Course_Fee"CREATE TABLE Learners (

CREATE TABLE Learners (
    Learner_Id INT AUTO_INCREMENT,
    LearnerFirstName VARCHAR(50) NOT NULL,
    LearnerLastName VARCHAR(50) NOT NULL,
    LearnerPhoneNo VARCHAR(15) NOT NULL,
    LearnerEmailId VARCHAR(50),
    LearnerEnrollmentDate TIMESTAMP NOT NULL,
    SelectedCourses INT NOT NULL,
    YearsOfExperience INT NOT NULL,
    LearnerCompany VARCHAR(50),
    SourceOfJoining VARCHAR(50) NOT NULL,
    Batch_Start_Date TIMESTAMP NOT NULL,
    Location VARCHAR(56) NOT NULL,
    PRIMARY KEY (Learner_Id),
    UNIQUE KEY (LearnerEmailId),
    FOREIGN KEY (SelectedCourses) REFERENCES Course(CourseID)
);

-- Inserting 10 sample learner records
-- Assume Batch_Start_Date for each learner follows your provided dates for each batch

INSERT INTO Learners (
    LearnerFirstName, LearnerLastName, LearnerPhoneNo, LearnerEmailId, 
    LearnerEnrollmentDate, SelectedCourses, YearsOfExperience, LearnerCompany, 
    SourceOfJoining, Batch_Start_Date, Location
) VALUES
    ('John', 'Doe', '1234567890', 'john.doe@example.com', '2024-01-01 09:00:00', 1, 2, 'Microsoft', 'Instagram Ads', '2024-06-25', 'New York'),
    ('Jane', 'Smith', '0987654321', 'jane.smith@example.com', '2024-01-02 10:00:00', 2, 3, 'Amazon', 'Youtube Ads', '2024-07-15', 'San Francisco'),
    ('Alice', 'Johnson', '1122334455', 'alice.johnson@example.com', '2024-01-03 11:00:00', 3, 1, 'Microsoft', 'Referral', '2024-08-13', 'Chicago'),
    ('Bob', 'Brown', '5566778899', 'bob.brown@example.com', '2024-01-04 12:00:00', 1, 4, 'Intel', 'Referral', '2024-06-25', 'Los Angeles'),
    ('Charlie', 'Davis', '6677889900', 'charlie.davis@example.com', '2024-01-05 13:00:00', 2, 5, 'Oracle', 'Youtube Ads', '2024-07-15', 'Houston'),
    ('Emma', 'Wilson', '2233445566', 'emma.wilson@example.com', '2024-01-06 14:00:00', 3, 2, 'Airbnb', 'Referral', '2024-08-13', 'Dallas'),
    ('Liam', 'Miller', '3344556677', 'liam.miller@example.com', '2024-01-07 15:00:00', 1, 3, 'Oracle', 'Referral', '2024-06-25', 'Austin'),
    ('Sophia', 'Moore', '4455667788', 'sophia.moore@example.com', '2024-01-08 16:00:00', 2, 2, 'Microsoft', 'Youtube Ads', '2024-07-15', 'Boston'),
    ('Olivia', 'Taylor', '5566778899', 'olivia.taylor@example.com', '2024-01-09 17:00:00', 3, 4, 'Microsoft', 'Youtube Ads', '2024-08-13', 'Seattle'),
    ('Ethan', 'Anderson', '6677889900', 'ethan.anderson@example.com', '2024-01-10 18:00:00', 1, 2, 'Amazon', 'Youtube Ads', '2024-06-25', 'Denver');

-- Read & Verify the inserted records
SELECT * FROM Learners;

Data Analysis [ Employee, Course, Learners ] Tables

1. Give me the record of the employee getting highest salary

• from employee Table

SELECT * FROM employee 
ORDER BY Salary DESC -- Ordering the results by the "Salary" column in descending order (highest salary first)
LIMIT 1  -- Limiting the result to only the first row (i.e., the highest salary)

2. Give me the record of the employee getting highest & Lowest salary

• from employee Table

SELECT MAX(Salary) as max_salary FROM employee 
SELECT MIN(Salary) as max_salary FROM employee 

/*
MAX - returns the highest value from the specified field name
MIN - returns the lowest value from the specified field name
AS - used to assign an alias (nickname) to the result of a column or expression, making the output more readable.
*/

ALTERNATIVE

SELECT * FROM employee
WHERE Salary = (
  -- Subquery to get the maximum salary
  SELECT MAX(Salary)
  FROM employee
) 
OR Salary = (
  -- Subquery to get the minimum salary
  SELECT MIN(Salary)
  FROM employee
);

3. Display the number of enrollments in the website of buildwithronnie database

• from learners Table

-- COUNT(*): This function counts the total number of rows in a table.
SELECT COUNT(*) AS num_of_enrollments
FROM Learners;

4. Display the number of enrollments for the courseID = 3

• from learners Table

-- Retrieve the count of learners enrolled in courseID = 3 i.e, 
-- Fullstack Interview Prep
SELECT COUNT(*) AS num_of_Learners_SQL
FROM Learners 
WHERE SelectedCourses=3;
/*
-- COUNT(*): Counts the total number of rows in the Learners table that meet the condition.
-- AS num_of_Learners_SQL: Sets an alias for the resulting count to improve readability.
-- WHERE SelectedCourses = 3: Filters the rows to only include learners who have exactly 3 selected courses.
*/

5. Count the number of learners enrolled in the month of Jan

• from learners Table

SELECT COUNT(*) as num_learners_jan 
FROM Learners 
WHERE LearnerEnrollmentDate LIKE '%-01-%'

/*
-- COUNT(*): Counts the total number of rows that satisfy the given condition.
-- AS num_learners_jan: Assigns an alias to the count result, indicating that 
it represents the number of January enrollments.
-- WHERE LearnerEnrollmentDate LIKE '%-01-%': Filters the rows where the 
LearnerEnrollmentDate includes -01-, assuming the date 
format includes month as 01 for January. 
This would match any date string containing -01-, 
allowing the query to count all January enrollments.
*/

6. Count the number of learners enrolled in the month of Jan 21

• from learners Table

SELECT COUNT(*) as num_learners_jan 
FROM Learners 
WHERE LearnerEnrollmentDate LIKE '%-01-21%'
/*
-- WHERE LearnerEnrollmentDate LIKE '%-01-21%': 
Filters the rows to match LearnerEnrollmentDate values that contain -01-21, 
assuming the date format includes -MM-YY. 
This will count all learners who enrolled in January of the year 2021.
*/

7. Update the Sophia data with years of experience as 1 and learner company as 'Amazon'

• from learners Table

Update Learners 
SET YearsofExperience = 2, LearnerCompany = 'Amazon'
WHERE Learner_ID = 4

/*
-- UPDATE Learners: Specifies the Learners table to modify.
-- SET YearsofExperience = 2, LearnerCompany = 'Amazon': 
Sets the YearsofExperience to 2 and LearnerCompany to 'Amazon' for the 
specified learner.
-- WHERE Learner_ID = 4: Applies the update only to the learner with 
Learner_ID equal to 4.
*/

8. Count the number of companies where learners currents doing there jobs

• from learners Table

-- Count all non-null entries in the LearnerCompany column
SELECT COUNT(LearnerCompany) FROM Learners

-- Count the number of unique (distinct) companies in the LearnerCompany column
SELECT COUNT(DISTINCT LearnerCompany) as distinct_companies  
FROM Learners

-- COUNT -> count the not null entries as well 
-- DISTINCT -> Unique Count 
-- COUNT(LearnerCompany): Counts all non-null entries in the LearnerCompany column, meaning it ignores any NULL values.
-- COUNT(DISTINCT LearnerCompany): Counts the number of unique (distinct) values in the LearnerCompany column, effectively giving the count of different companies without duplicates.