SQL
DATABASE MANAGEMENT SYSTEM
TOPIC RECAP
SQL:
SQL is a standard database language used to access and manipulate data in databases. SQL stands
for Structured Query Language. It was developed by IBM Computer Scientists in the 1970s. By
executing queries SQL can create, update, delete, and retrieve data in databases like MySQL, Oracle,
PostgreSQL, etc. Overall, SQL is a query language that communicates with databases.
What are SQL commands?
Developers use structured query language (SQL) commands, which are specific keywords or SQL
statements, to work with data stored in relational databases. The following are categories for SQL
commands.
1. Data Definition Language
SQL commands used to create the database structure are known as data definition language (DDL).
Based on the needs of the business, database engineers create and modify database objects using
DDL. The CREATE command, for instance, is used by the database engineer to create database
objects like tables, views, and indexes.
Command Description
CREATE Creates a new table, a view of a table, or other object in the database.
ALTER Modifies an existing database object, such as a table
DROP Deletes an entire table, a view of a table, or other objects in the database
2. Data Manipulation Language
A relational database can be updated with new data using data manipulation language (DML)
statements. The INSERT command, for instance, is used by an application to add a new record to the
database.
Command Description
SELECT Retrieves certain records from one or more tables.
INSERT Creates a record.
UPDATE Modifies records.
Command Description
DELETE Deletes records.
3. Data Query Language
Data retrieval instructions are written in the data query language (DQL), which is used to access
relational databases. The SELECT command is used by software programs to filter and return results
from a SQL table.
4. Data Control language
Data control language (DCL) is a programming language used by database administrators to control
or grant other users access to databases. For instance, they can allow specific applications to
manipulate one or more tables by using the GRANT command.
Command Description
GRANT Gives a privilege to the user.
REVOKE Takes back privileges granted by the user.
5. Transaction Control Language
To automatically update databases, the relational engine uses transaction control language (TCL).
For instance, the database can reverse a mistaken transaction using the ROLLBACK command.
Uses of SQL
SQL is used for interacting with databases. These interactions include:
1. Data definition: It is used to define the structure and organization of the stored data and the
relationships among the stored data items.
2. Data retrieval: SQL can also be used for data retrieval.
3. Data manipulation: If the user wants to add new data, remove data, or modifying in existing
data then SQL provides this facility also.
4. Access control: SQL can be used to restrict a user’s ability to retrieve, add, and modify data,
protecting stored data against unauthorized access.
5. Data sharing: SQL is used to coordinate data sharing by concurrent users, ensuring that changes
made by one user do not inadvertently wipe out changes made at nearly the same time by another
user.
SQL Data Types
The data type guideline for SQL is to understand what type of data is expected inside each column and it
also identifies how SQL will interact with the stored data.
Numeric Datatypes
Date and Time Datatypes
String Datatypes
SQL Injection
A cyberattack known as SQL injection involves tricking the database with SQL queries. To retrieve,
alter, or corrupt data in a SQL database, hackers use SQL injection. To execute a SQL injection attack,
for instance, they might enter a SQL query in place of a person’s name in a submission form.
Key concepts Revisited:
What is a database management system (DBMS)?
A database management system (DBMS) is a software system for creating and managing databases.
A database enables end users to create, protect, update, and delete data in a database.
An interface for operations like creation, deletion, modification, etc is provided by DBMS.
DBMS allows the user to create their databases as per their requirement.
DBMS accepts the request from the application and provides specific data through the
operating system.
DBMS contains a group of programs that acts according to the user’s instruction.
It provides security to the database.
DBMS can be classified into two main types:
1. Relational Database Management System.
Considered the most mature of all databases, these databases lead in the production line along
with their management systems. In this database, every piece of information has a relationship
with every other piece of information. This is on account of every data value in the database
having a unique identity in the form of a record.
Note that all data is tabulated in this model. Therefore, every row of data in the database is
linked with another row using a primary key. Similarly, every table is linked with another table
using a foreign key.
2. Non-Relational Database Management System.
A NoSQL originally referring to non-SQL or non-relational is a database that provides a
mechanism for storage and retrieval of data. This data is modelled in means other than the
tabular relations used in relational databases.
A NoSQL database includes simplicity of design, simpler horizontal scaling to clusters of
machines, and finer control over availability. The data structures used by NoSQL databases are
different from those used by default in relational databases which makes some operations faster
in NoSQL. The suitability of a given NoSQL database depends on the problem it should solve.
Data structures used by NoSQL databases are sometimes also viewed as more flexible than
relational database tables.
MongoDB falls in the category of NoSQL document-based database.
Advantages and Disadvantages of DBMS
Advantages
Data Security.
Data Integration.
Reduction in Data Redundancy.
Data abstraction.
Disadvantages
Increased cost.
Performance.
Frequency upgrade/Replacement cycles.
Complex design.
Why is RDBMS Required?
RDBMS on the other hand is a type of DBMS, as the name suggests it deals with relations as well as
various key constraints. So here we have tables which are called schema and we have rows which are
called tuples. It also aids in the reduction of data redundancy and the preservation of database
integrity.
Relational Database Management System is an advanced version of a DBMS. RDBMS stands for
Relational Database Management Systems.
CRUD Operations in SQL
As we know, CRUD operations act as the foundation of any computer programming language or technology.
So before taking a deeper dive into any programming language or technology, one must be proficient in
working on its CRUD operations. This same rule applies to databases as well.
1. Create:
In CRUD operations, 'C' is an acronym for create, which means to add or insert data into the SQL table. So,
firstly we will create a table using CREATE command and then we will use the INSERT INTO command to
insert rows in the created table.
2. Read:
In CRUD operations, 'R' is an acronym for read, which means retrieving or fetching the data from the SQL
table. So, we will use the SELECT command to fetch the inserted records from the SQL table. We can
retrieve all the records from a table using an asterisk (*) in a SELECT query. There is also an option of
retrieving only those records which satisfy a particular condition by using the WHERE clause in a SELECT
query.
3. Update:
In CRUD operations, 'U' is an acronym for the update, which means making updates to the records present
in the SQL tables. So, we will use the UPDATE command to make changes in the data present in tables.
4. Delete:
In CRUD operations, 'D' is an acronym for delete, which means removing or deleting the records from the
SQL tables. We can delete all the rows from the SQL tables using the DELETE query. There is also an
option to remove only the specific records that satisfy a particular condition by using the WHERE clause in
a DELETE query.
Application and Use-case:
APPLICATIONS:
SQL (Structured Query Language) is a database management system (DBMS) that's used to manage and
manipulate relational data. SQL is used in a wide range of applications, including:
Data analytics: SQL is used to analyze data, extract insights, and make decisions.
Data science: SQL is used to create predictive models, prepare data for machine learning, and
visualize data.
Data storage: SQL is used to store structured data in tables, rows, and columns.
Data processing: SQL is used to query, filter, sort, and aggregate data.
Data integration: SQL can be integrated with other tools and languages, such as Python and R.
Reporting: SQL can be used to generate reports and datasets for visualization tools.
Real-time data science: SQL can be used for real-time applications like fraud detection,
dynamic pricing, and recommendation systems.
USE-CASE:
SQL Uses in Marketing: Marketing teams often target customers and release promotions based on
user data collected by the organization.
SQL Uses in Finance: Organizations often store financial data in databases since they are highly
secure and can be queried easily. Finance professionals can extract this data to make data-driven
decisions.
SQL Uses in Healthcare: SQL is often used to manipulate and analyze patient
information stored in clinical databases.
PRACTISE EXERCISES:
Beginner Level:
Write a query to retrieve all the columns from a table named employees.
Retrieve the names of employees from the employees table who work in the "Sales" department.
Display all product details from the products table sorted by price in descending order.
Find the total number of employees in the employees’ table.
Count the number of employees in each department.
Intermediate Level:
Retrieve the names of employees and the names of their respective managers from the employees
table and managers’ table.
Find the names of employees earning a salary higher than the average salary in the company.
Increase the salary of employees in the "IT" department by 10%.
Remove employees who have not been active since 2020.
Write a query to categorize employees into "High", "Medium", and "Low" salary bands.
Advanced Level:
Rank employees based on their salaries within each department.
List all customers who placed orders in the last month, along with the total amount spent.
Find the second-highest salary in the employees table using a CTE.
Knowledge Check:
1. Which SQL query retrieves data from a table and sorts it in descending order by a column named age?
A) SELECT * FROM students ORDER BY age;
B) SELECT * FROM students SORT DESC age;
C) SELECT * FROM students ORDER BY age DESC;
D) SELECT * FROM students SORTED BY age DESC;
2. Which of the following is NOT an aggregate function in SQL?
A) COUNT()
B) SUM()
C) AVG()
D) WHERE
3. Which type of JOIN returns all records from both tables, including unmatched rows?
A) INNER JOIN
B) LEFT JOIN
C) RIGHT JOIN
D) FULL OUTER JOIN
4. Which SQL constraint is used to ensure that all values in a column are unique?
A) PRIMARY KEY
B) FOREIGN KEY
C) UNIQUE
D) NOT NULL
5. Which SQL statement is used to delete a record from a table?
A) REMOVE FROM
B) DELETE FROM
C) DROP
D) TRUNCATE
Further Reading/Resources:
PostgreSQL: PostgreSQL is a powerful, open-source object-relational database system with over 35
years of active development that has earned it a strong reputation for reliability, feature robustness, and
performance.
[Link]
SQL tutorial:
[Link]