SQL Basics and Data Retrieval
Learning Objectives:
• Understand SQL syntax and structure
• Execute basic SQL queries to retrieve data
• Use various SQL clauses and functions to filter and sort data
Topics Covered:
1. Introduction to SQL
• Overview of SQL: What is SQL, and why is it important?
• SQL databases and tables
2. Basic SQL Queries
• Writing basic SELECT statements
• Selecting specific columns
• Using AS for column aliases
3. Filtering Data
• Using WHERE clause to filter records
• Using logical operators (AND, OR, NOT)
• Using comparison operators (=, !=, <, >, <=, >=)
4. Sorting Data
• Sorting results using ORDER BY
• Sorting by multiple columns
• Sorting in ascending (ASC) and descending (DESC) order
5. Using SQL Functions
• Basic arithmetic operations in SQL
• Using SQL functions (COUNT, SUM , AVG, MIN, MAX)
• Using DISTINCT to remove duplicate records
Overview of SQL
What is SQL?
SQL (Structured Query Language) is a standardized programming language used to manage and
manipulate relational databases. It allows users to create, read, update, and delete data stored in a
relational database management system (RDBMS). SQL is designed for managing data in a
relational model, which organizes data into tables consisting of rows and columns.
Key Features of SQL:
1. Data Definition Language (DDL):
o CREATE: Creates new tables, databases, indexes, or views.
o ALTER: Modifies existing database structures, such as adding a column to a
table.
o DROP: Deletes tables, databases, indexes, or views.
2. Data Manipulation Language (DML):
o SELECT: Retrieves data from one or more tables.
o INSERT: Adds new data into a table.
o UPDATE: Modifies existing data within a table.
o DELETE: Removes data from a table.
3. Data Control Language (DCL):
o GRANT: Provides specific privileges to users or roles.
o REVOKE: Removes previously granted privileges.
4. Transaction Control Language (TCL):
o COMMIT: Saves all changes made during the current transaction.
o ROLLBACK: Reverts changes made during the current transaction.
Why is SQL Important?
1. Standardization: SQL is a widely accepted standard for relational database
management, supported by many RDBMSs like MySQL, PostgreSQL, SQL Server, and
Oracle.
2. Data Management: SQL provides a robust, efficient, and reliable way to manage large
amounts of data.
3. Data Manipulation: It allows for complex querying and data manipulation, enabling
users to retrieve specific data, update records, and perform aggregate calculations.
4. Integration: SQL integrates well with various programming languages (e.g., Python,
Java, PHP) and tools, making it versatile for application development.
5. Data Security: Through SQL, administrators can control access to data, ensuring that
sensitive information is protected.
6. Transactional Control: SQL supports transactions, allowing multiple operations to be
executed as a single unit, ensuring data integrity.
7. Analytics: SQL is essential for data analytics, providing powerful querying capabilities
to extract insights from data.
SQL Databases and Tables
SQL Databases
A database in SQL is a structured collection of data. It contains objects like tables, views,
indexes, stored procedures, and other components necessary for data management. A SQL
database is managed by a Database Management System (DBMS), such as MySQL,
PostgreSQL, SQL Server, or Oracle.
Key Concepts:
• Schema: A schema is a logical container for database objects, defining how data is organized and
related within the database.
• Tables: The primary structure in a database where data is stored.
• Indexes: Structures that improve the speed of data retrieval operations.
• Views: Virtual tables created by querying data from one or more tables.
• Stored Procedures: Precompiled SQL statements that perform specific tasks.
Schema Example:
Basic SQL Queries
SQL queries are used to perform various operations on the data stored in a database. Here are
some of the most fundamental SQL queries that you should know.
1. SELECT
The SELECT statement is used to retrieve data from a database. The result is stored in a result
table, sometimes called the result set.
2. WHERE
The WHERE clause is used to filter records. It is used to extract only those records that fulfill a
specified condition.
3. INSERT INTO
The INSERT INTO statement is used to add new records to a table.
4. UPDATE
The UPDATE statement is used to modify existing records in a table.
5. DELETE
The DELETE statement is used to delete existing records in a table.
6. ORDER BY
The ORDER BY keyword is used to sort the result set in either ascending or descending order.
7. LIMIT (MySQL, PostgreSQL) / TOP (SQL Server)
The LIMIT or TOP clause is used to specify the number of records to return.
8. JOIN
The JOIN clause is used to combine rows from two or more tables, based on a related column
between them.
9. GROUP BY
The GROUP BY statement groups rows that have the same values into summary rows, like "find
the number of employees in each department."
10. HAVING
The HAVING clause was added to SQL because the WHERE keyword could not be used with
aggregate functions.
SQL SELECT Operators
Operators in SQL are used within the SELECT statement to perform various operations on data,
such as arithmetic calculations, comparisons, and logical operations. Here are some of the key
operators used in SQL:
1. Arithmetic Operators
Arithmetic operators perform mathematical operations on numeric data.
2. Comparison Operators
Comparison operators compare two values and return a boolean result ( TRUE or FALSE).
3. Logical Operators
Logical operators combine two or more conditions.
4. BETWEEN Operator
The BETWEEN operator selects values within a given range.
5. IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
6. LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
7. IS NULL Operator
The IS NULL operator is used to test for empty values (NULL).
8. EXISTS Operator
The EXISTS operator is used to test for the existence of any record in a subquery.
SELECT *
FROM Employees
WHERE EXISTS (SELECT * FROM Departments WHERE [Link] =
[Link]);
