All Sql Queries With Examples Pdf

Understanding SQL and Its Importance in Database Management

Structured Query Language (SQL) is the standard language for managing and manipulating databases. Whether you’re a database administrator, a developer, or just someone who works with data, understanding SQL is crucial for querying, updating, and managing data in relational database management systems (RDBMS). SQL queries are used to perform tasks such as retrieving data from a database, inserting records, updating records, and deleting records.

Basic SQL Queries: Retrieving Data with SELECT

The SELECT statement is one of the most commonly used SQL commands. It allows you to specify exactly which data you want to retrieve from a database. Here’s a simple example of a SELECT statement:

SELECT first_name, last_name FROM employees;

This query retrieves the first_name and last_name columns from the employees table. If you want to retrieve all columns from a table, you can use the asterisk (*) wildcard:

SELECT * FROM employees;

Filtering Data with WHERE Clause

To filter records and fetch only those that meet a specific criterion, the WHERE clause is used in conjunction with the SELECT statement:

SELECT * FROM employees WHERE department = 'Sales';

This query will return all records from the employees table where the department column has the value ‘Sales’.

Sorting Results with ORDER BY

The ORDER BY clause is used to sort the result set of a query by one or more columns. You can sort the data in ascending order (which is the default) or descending order by using the ASC or DESC keywords, respectively.

SELECT first_name, last_name FROM employees ORDER BY last_name ASC;

This query sorts the employees by their last_name in ascending order.

Combining Conditions with AND, OR, and NOT

You can combine multiple conditions in a WHERE clause using the logical operators AND, OR, and NOT. These operators allow for more complex queries.

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

This query retrieves all employees in the ‘Sales’ department with a salary greater than 50,000.

Joining Tables with JOIN

SQL joins are used to combine rows from two or more tables, based on a related column between them. There are different types of joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

SELECT employees.first_name, employees.last_name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

This query uses an INNER JOIN to retrieve the first name and last name of employees along with the name of their respective departments.

Grouping Data with GROUP BY

The GROUP BY statement groups rows that have the same values in specified columns into summary rows, like “find the number of employees in each department”.

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

This query counts the number of employees in each department.

Filtering Grouped Data with HAVING

The HAVING clause is used in combination with the GROUP BY clause to filter groups based on a specified condition.

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

This query selects departments with more than 10 employees.

Inserting Data with INSERT INTO

The INSERT INTO statement is used to insert new records into a table.

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'Sales', 60000);

This query inserts a new record into the employees table.

Updating Data with UPDATE

The UPDATE statement is used to modify existing records in a table.

UPDATE employees
SET salary = 70000
WHERE first_name = 'John' AND last_name = 'Doe';

This query updates the salary of an employee named John Doe to 70,000.

Deleting Data with DELETE

The DELETE statement is used to delete existing records from a table.

DELETE FROM employees WHERE first_name = 'John' AND last_name = 'Doe';

This query deletes the record of an employee named John Doe from the employees table.

Using Subqueries to Nest Queries

A subquery is a query within another SQL query and is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name = 'IT');

This query selects all employees who work in the IT department by using a subquery.

Combining Results with UNION

The UNION operator is used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements.

SELECT first_name FROM employees
UNION
SELECT name FROM departments;

This query combines the list of employee first names with department names, removing duplicates.

SQL Aggregate Functions

SQL provides several aggregate functions that perform a calculation on a set of values and return a single value. Some of the most common aggregate functions include COUNT(), SUM(), AVG() (average), MAX() (maximum), and MIN() (minimum).

SELECT AVG(salary) as average_salary FROM employees;

This query calculates the average salary of all employees.

Using SQL Functions for Data Manipulation

SQL provides many built-in functions for performing operations on data. These include string functions, numeric functions, date functions, and more.

SELECT UPPER(first_name), LOWER(last_name) FROM employees;

This query returns the first names of employees in uppercase and last names in lowercase.

Creating Tables with CREATE TABLE

The CREATE TABLE statement is used to create a new table in the database.

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

This query creates a new table called departments with an id column as the primary key and a name column.

Modifying Tables with ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE employees ADD COLUMN bio TEXT;

This query adds a new column called bio to the employees table.

Setting Constraints with SQL

Constraints are used in SQL to specify rules for the data in a table. Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.

CREATE TABLE roles (
    role_id INT PRIMARY KEY,
    role_name VARCHAR(255) NOT NULL UNIQUE,
    CHECK (role_name  '')
);

This query creates a new table called roles with constraints on the role_name column to ensure it is unique and not empty.

Transactional Control with COMMIT and ROLLBACK

SQL transactions are used to manage changes made by DML statements. The COMMIT statement is used to save the changes, while the ROLLBACK statement is used to undo them.

BEGIN TRANSACTION;
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Jane', 'Smith', 'Marketing', 50000);
COMMIT;

This query starts a transaction, inserts a new employee record, and then commits the transaction to save the changes.

Indexing for Performance Optimization

Indexes are used to retrieve data from the database more quickly. They are created using the CREATE INDEX statement.

CREATE INDEX idx_lastname ON employees (last_name);

This query creates an index on the last_name column of the employees table to improve the performance of queries that search by last name.

Frequently Asked Questions (FAQ)

• What is the difference between WHERE and HAVING?

  WHERE is used to filter rows before any groupings are made, while HAVING is used to filter groups after the GROUP BY clause has been applied.

• Can you use aliases in SQL queries?

  Yes, aliases can be used to give a table or a column a temporary name to make queries more readable.

• What is a primary key?

  A primary key is a field in a table which uniquely identifies each row/record in that table. Primary keys must contain unique values and cannot contain NULL values.

• How do you handle NULL values in SQL?

  NULL values can be checked using the IS NULL and IS NOT NULL operators in SQL.

• What is a SQL injection?

  SQL injection is a code injection technique that might destroy your database. It is one of the most common web hacking techniques. It can be prevented by using parameterized queries and prepared statements.

SQL is a powerful tool for managing and manipulating data in databases. By understanding and using the various SQL queries and commands, you can effectively interact with databases to perform a wide range of data operations. Whether you’re creating tables, inserting data, or querying complex datasets, SQL provides the functionality needed to work with data efficiently and effectively.