Day 11 of 15 Days of Advanced SQL Series
Welcome back peeps. Hope all’s well. Last two weeks have been crazy busy at work for me (plus I was traveling).
Day 2 : SQL Basics, Query Structure, Built In functions Conditions
Day 4 : Set Theory Operations, Stored Procedures and CASE statements in SQL
Day 6 : Subqueries, Group by, order by and Having clauses in SQL and Analytical Functions
Day 7 : Window Functions, Grouping Sets and Constraints in SQL
Day 8 : BigQuery Basics, SELECT, FROM, WHERE and Date and Extract in BigQuery
Day 9 : Common Expression Table, UNNEST Clause, SQL vs NoSQL Databases
Day 10 : Triggers, Pivot and Cursors in SQL
Day 14 : MySQL in Depth
Day 15 : PostgreSQL inDepth
Anyways, For Day 11 of 15 days of Advanced SQL, we will cover —
Views in SQL
Indexes in SQL
Auto Increment in SQL
Github for Advanced SQL that you can follow —
Projects Videos —
All the projects, data structures, SQL, algorithms, system design, Data Science and ML , Data Analytics, Data Engineering, , Implemented Data Science and ML projects, Implemented Data Engineering Projects, Implemented Deep Learning Projects, Implemented Machine Learning Ops Projects, Implemented Time Series Analysis and Forecasting Projects, Implemented Applied Machine Learning Projects, Implemented Tensorflow and Keras Projects, Implemented PyTorch Projects, Implemented Scikit Learn Projects, Implemented Big Data Projects, Implemented Cloud Machine Learning Projects, Implemented Neural Networks Projects, Implemented OpenCV Projects,Complete ML Research Papers Summarized, Implemented Data Analytics projects, Implemented Data Visualization Projects, Implemented Data Mining Projects, Implemented Natural Leaning Processing Projects, MLOps and Deep Learning, Applied Machine Learning with Projects Series, PyTorch with Projects Series, Tensorflow and Keras with Projects Series, Scikit Learn Series with Projects, Time Series Analysis and Forecasting with Projects Series, ML System Design Case Studies Series videos will be published on our youtube channel ( just launched).
Subscribe today!
System Design Case Studies — In Depth
Complete Data Structures and Algorithm Series
Github —
Let’s get started with Day 11.
- Views in SQL: A view in SQL is a virtual table that is based on the result of a SELECT statement. It does not store data itself, but instead references the data in one or more underlying tables. Views can be used to simplify complex queries, to restrict access to certain columns of a table, or to combine data from multiple tables into a single virtual table.
- Indexes in SQL: An index in SQL is a database object that is used to improve the performance of queries. It is a data structure that allows the database management system to quickly find and retrieve specific rows based on the values in one or more columns. There are several types of indexes in SQL, including clustered indexes, non-clustered indexes, and full-text indexes.
- Auto Increment in SQL: Auto increment is a feature in SQL that allows a column to automatically generate a unique value for each new row added to a table. The most common use of auto increment is to create a primary key column that is unique for each row in a table. The AUTO_INCREMENT attribute is used to specify that the column should be auto-incremented. The default starting value is 1, and it will increment by 1 for each new row.
Implementation —
-- Create a table for storing employee data
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary INT
);
-- Insert sample data into the employees table
INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 50000),
('Jane', 'Smith', 60000),
('Michael', 'Johnson', 70000);
-- 1. Views: Creating a view to simplify complex queries
-- Create a view to retrieve employee names and salaries
CREATE VIEW employee_info AS
SELECT first_name, last_name, salary
FROM employees;
-- Retrieve data from the view
SELECT * FROM employee_info;
-- 2. Indexes: Creating an index for faster query performance
-- Create an index on the salary column of the employees table
CREATE INDEX idx_employees_salary ON employees (salary);
-- Retrieve data from the employees table using the index
SELECT *
FROM employees
WHERE salary > 60000;
-- 3. Auto Increment: Using auto increment for generating unique IDs
-- Insert a new row into the employees table with an auto-generated ID
INSERT INTO employees (first_name, last_name, salary)
VALUES ('Emily', 'Johnson', 80000);
-- Retrieve the data from the employees table
SELECT * FROM employees;Snippet —
Views in SQL
Views are important constructs in SQL. A view is nothing but a temporary virtual table created from one or more tables, based on the result returned by the SQL statement.
Views are helpful in creating complex queries and implementing readability in the SQL statements. Views also help in maintaining data integrity as it shows an accurate and consistent views of data in the database. Most importantly, this helps in providing better security to the data.
Implementation —
-- Creating a View Example
-- Create a table for employee data
CREATE TABLE Employee (
employee_id INT,
employee_name VARCHAR(50),
employee_salary INT
);
-- Create a table for company data
CREATE TABLE Company (
employee_id INT,
employee_address VARCHAR(50),
employee_salary INT
);
-- Insert sample data into the tables
INSERT INTO Employee (employee_id, employee_name, employee_salary)
VALUES (1, 'John Doe', 50000),
(2, 'Jane Smith', 60000),
(3, 'Michael Johnson', 70000);
INSERT INTO Company (employee_id, employee_address, employee_salary)
VALUES (1, '123 Main St', 5000),
(2, '456 Elm St', 6000),
(3, '789 Oak St', 7000);
-- Create a view that combines data from both tables
CREATE VIEW EmployeeCompany AS
SELECT e.employee_name, c.employee_address, c.employee_salary
FROM Employee e
INNER JOIN Company c ON e.employee_id = c.employee_id;
-- Query the view
SELECT * FROM EmployeeCompany;
-- Update the view definition
CREATE OR REPLACE VIEW EmployeeCompany AS
SELECT e.employee_name, c.employee_address, c.employee_salary
FROM Employee e
INNER JOIN Company c ON e.employee_id = c.employee_id
WHERE c.employee_salary > 6000;
-- Query the updated view
SELECT * FROM EmployeeCompany;
-- Delete the view
DROP VIEW EmployeeCompany;Syntax —
CREATE VIEW view_name AS SELECT t1, t2, t3…tn ( column_name) FROM table WHERE condition
To update a view —
CREATE OR REPLACE VIEW view_name AS SELECT t1, t2, t3…tn ( column_name) FROM table WHERE condition
Example —
CREATE VIEW [Salary_sum] AS SELECT employee_name, employee_salary FROM Employee WHERE employee_salary > (SELECT SUM(employee_salary) FROM Employee)
To query from the view created —
SELECT * FROM Salary_sum
It will show all the results from the view Salary_sum.
To Delete a View —
DROP VIEW Salary_sum
Create a view from multiple Tables —
CREATE VIEW view_name AS
SELECT Employee.employee_name, Company. employee_address, Company.employee_salary
FROM Employee INNER JOIN Company
WHERE Employee.employee_name = Company.employee_name
Snippet —
Indexes in SQL
Indexes are very useful as they help speed up the query execution and helps faster retrieval of the data.
Syntax —
CREATE INDEX index_name
ON table (column_1, column_2, …column_n)
There are two types of indexes —
- Implicit indexes — indexes that are created by databases internally to store, retrieve faster and efficiently.
- Composite indexes — indexes that are created by using multiple columns to uniquely identify the data points.
Syntax —
CREATE UNIQUE INDEX index_name
ON tableName (column1, column2, …)
Implementation —
-- Creating Indexes Example
-- Create a table for storing employee data
CREATE TABLE Employee (
employee_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary INT,
city VARCHAR(50)
);
-- Insert sample data into the employee table
INSERT INTO Employee (employee_id, emp_name, salary, city)
VALUES (1, 'John Doe', 50000, 'New York'),
(2, 'Jane Smith', 60000, 'Los Angeles'),
(3, 'Michael Johnson', 70000, 'Chicago');
-- Create an index on the emp_name column
CREATE INDEX emp_name_idx ON Employee (emp_name);
-- Create a unique index on multiple columns
CREATE UNIQUE INDEX emp_salary_city_idx ON Employee (salary, city);
-- Query the employee table using the index
SELECT * FROM Employee WHERE emp_name = 'John Doe';
-- Drop an index
DROP INDEX emp_name_idx ON Employee;Example —
CREATE INDEX emp_indx
ON employee(emp_name, salary, city)
To drop the indexes —
ALTER TABLE table
DROP INDEX index_name
Snippet —
Auto Increment in SQL
Autoincrement helps to identify the records very easily once set on a particular row(s). These are assigned to the primary key to make sure the data has a unique identification.
Syntax —
CREATE TABLE tableName (
column_1 NOT NULL AUTO_INCREMENT,
t1, t2, t3 ( column_names)……
PRIMARY KEY(col1))
Implementation —
-- Creating a Table with Auto Increment Example
-- Create a table for storing customer data
CREATE TABLE Customer (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50)
);
-- Insert sample data into the Customer table
INSERT INTO Customer (first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]'),
('Jane', 'Smith', '[email protected]'),
('Michael', 'Johnson', '[email protected]');
-- Retrieve data from the Customer table
SELECT * FROM Customer;
-- Insert a new row into the Customer table with auto-generated customer_id
INSERT INTO Customer (first_name, last_name, email)
VALUES ('Emily', 'Davis', '[email protected]');
-- Retrieve the data from the Customer table
SELECT * FROM Customer;Example —
CREATE TABLE Employee(
employee_id int NOT NULL auto_increment,
employee_name varchar(60),
city varchar(10),
PRIMARY KEY(employee_id))
Snippet —
Complete Implementation —
-- Creating Views, Indexes, and Auto Increment Example
-- Create a table for storing employee data
CREATE TABLE Employee (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(50),
salary INT,
city VARCHAR(50)
);
-- Insert sample data into the employee table
INSERT INTO Employee (emp_name, salary, city)
VALUES ('John Doe', 50000, 'New York'),
('Jane Smith', 60000, 'Los Angeles'),
('Michael Johnson', 70000, 'Chicago');
-- Create a view that shows employees with salaries above a certain threshold
CREATE VIEW HighSalaryEmployees AS
SELECT emp_name, salary, city
FROM Employee
WHERE salary > 60000;
-- Query the HighSalaryEmployees view
SELECT * FROM HighSalaryEmployees;
-- Create an index on the emp_name column of the Employee table
CREATE INDEX emp_name_idx ON Employee (emp_name);
-- Query the employee table using the emp_name index
SELECT * FROM Employee WHERE emp_name = 'John Doe';
-- Insert a new employee into the Employee table with auto-generated employee_id
INSERT INTO Employee (emp_name, salary, city)
VALUES ('Emily Davis', 55000, 'San Francisco');
-- Retrieve the data from the Employee table
SELECT * FROM Employee;Snippet —
That’s it for now.
Find Day 12 Below:
Let me know if you have questions in the comment section below. Subscribe/ Follow, Like/Clap as it would encourage me to write more in my free time
Stay Tuned!!
Read More —
11 most important System Design Base Concepts
6. Networking, How Browsers work, Content Network Delivery ( CDN)
13. System Design Template — How to solve any System Design Question
Some of the other best Series —
30 days of Data Structures and Algorithms and System Design Simplified
Data Science and Machine Learning Research ( papers) Simplified **
100 days : Your Data Science and Machine Learning Degree Series with projects
Complete Data Visualization and Pre-processing Series with projects
Exceptional Github Repos — Part 1
Exceptional Github Repos — Part 2
Tech Newsletter —
If you are interested, you can join my newsletter through which I send tech interview tips, techniques, patterns, hacks — Software Development, ML, Data Science, Startups and Technology projects to more than 30K readers. You can subscribe to Tech Brew :
For Python Projects —
For complete 60 days of Data Science and ML : Day 1 — Day 60 : Quick Recap of 60 days of Data Science and ML
Follow for more updates. Stay tuned and keep coding!
For other projects, tune to —
Build Machine Learning Pipelines( With Code)
Recurrent Neural Network with Keras
Clustering Geolocation Data in Python using DBSCAN and K-Means
Facial Expression Recognition using Keras
Hyperparameter Tuning with Keras Tuner
Custom Layers in Keras
