All Sql Query With Example Pdf

Understanding the Basics of SQL Queries

Structured Query Language (SQL) is the standard language for managing and manipulating databases. Whether you’re a database administrator, developer, or data analyst, understanding SQL queries is essential for interacting with relational databases. In this article, we’ll explore various SQL queries with practical examples to help you grasp the concepts and apply them effectively.

SQL Data Definition Language (DDL)

The Data Definition Language (DDL) part of SQL allows users to define and modify the database structure. Common DDL statements include CREATE, ALTER, and DROP.

CREATE – Used to create a new table or database.

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

ALTER – Used to modify an existing table, such as adding a new column.
```
ALTER TABLE students ADD COLUMN email VARCHAR(100);
```
DROP – Used to delete a table or database.
```
DROP TABLE IF EXISTS students;
```

SQL Data Manipulation Language (DML)

The Data Manipulation Language (DML) includes commands that allow users to insert, update, delete, and retrieve data from the database.

INSERT – Adds new rows to a table.

INSERT INTO students (id, name, age) VALUES (1, 'Alice', 22);

UPDATE – Modifies existing data within a table.
```
UPDATE students SET age = 23 WHERE id = 1;
```
DELETE – Removes rows from a table.
```
DELETE FROM students WHERE id = 1;
```
SELECT – Retrieves data from one or more tables.
```
SELECT * FROM students;
```

Retrieving Data with SELECT Queries

The SELECT statement is one of the most frequently used SQL commands. It allows you to specify exactly which data you want to retrieve from the database.

Selecting specific columns:
```
SELECT name, age FROM students;
```
Filtering results with WHERE:
```
SELECT * FROM students WHERE age >= 18;
```

Sorting results with ORDER BY:

SELECT * FROM students ORDER BY age DESC;

Limiting results with LIMIT:
```
SELECT * FROM students LIMIT 5;
```

Joining Tables in SQL

SQL joins are used to combine rows from two or more tables, based on a related column between them. Here are the different types of joins:

INNER JOIN – Selects records that have matching values in both tables.

SELECT students.name, enrollments.course_id
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id;

LEFT JOIN (or LEFT OUTER JOIN) – Returns all records from the left table, and the matched records from the right table.

SELECT students.name, enrollments.course_id
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id;

RIGHT JOIN (or RIGHT OUTER JOIN) – Returns all records from the right table, and the matched records from the left table.

SELECT students.name, enrollments.course_id
FROM students
RIGHT JOIN enrollments ON students.id = enrollments.student_id;

FULL OUTER JOIN – Returns all records when there is a match in either left or right table.

SELECT students.name, enrollments.course_id
FROM students
FULL OUTER JOIN enrollments ON students.id = enrollments.student_id;

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 students in each age group”.

SELECT age, COUNT(*) AS number_of_students
FROM students
GROUP BY age;

Filtering Groups with HAVING

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. It is used to filter groups based on a condition.

SELECT age, COUNT(*) AS number_of_students
FROM students
GROUP BY age
HAVING COUNT(*) > 5;

Combining Queries with UNION

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types.

SELECT name FROM students
UNION
SELECT name FROM teachers;

Using Subqueries in SQL

A subquery is a query nested inside another query. It can be used in various places such as in the SELECT, FROM, or WHERE clause.

SELECT name
FROM students
WHERE id IN (SELECT student_id FROM enrollments WHERE course_id = 'CS101');

SQL Transactions

Transactions are important for maintaining data integrity. They are a sequence of one or more SQL operations performed as a single logical unit of work.

BEGIN TRANSACTION – Starts a transaction.
```
BEGIN TRANSACTION;
```
COMMIT – Saves the changes.
```
COMMIT;
```
ROLLBACK – Reverts the changes.
```
ROLLBACK;
```

SQL Functions

SQL provides many built-in functions for performing calculations on data, such as COUNT(), SUM(), AVG(), MIN(), and MAX().

Counting rows in a table:
```
SELECT COUNT(*) FROM students;
```
Calculating the sum of a column:
```
SELECT SUM(age) FROM students;
```
Finding the average value:
```
SELECT AVG(age) FROM students;
```
Identifying the minimum and maximum values:
```
SELECT MIN(age), MAX(age) FROM students;
```

Advanced SQL Query Techniques

Window Functions

Window functions perform a calculation across a set of table rows that are somehow related to the current row. They are used for tasks such as ranking or running totals.

SELECT name, age, RANK() OVER (ORDER BY age DESC) AS rank
FROM students;

Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

WITH ranked_students AS (
  SELECT name, age, RANK() OVER (ORDER BY age DESC) AS rank
  FROM students
)
SELECT * FROM ranked_students WHERE rank <= 3;

Pivoting Data with CASE Statements

The CASE statement in SQL acts like a switch statement in programming languages, allowing for conditional logic in queries.

SELECT name,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age BETWEEN 18 AND 65 THEN 'adult'
    ELSE 'senior'
  END AS age_group
FROM students;

Frequently Asked Questions

How do I export SQL query results to a PDF?

To export SQL query results to a PDF, you would typically need to use a reporting tool or software that supports SQL to PDF conversion. Some database management systems (DBMS) like Microsoft SQL Server have built-in features or additional tools like SQL Server Reporting Services (SSRS) that can generate reports in PDF format. Alternatively, you can use programming languages like Python with libraries such as ReportLab to create PDFs from SQL query results.

Can I use SQL to query non-relational databases?

While SQL is designed for relational databases, some non-relational databases, like MongoDB and Cassandra, offer SQL-like query languages or interfaces that allow you to perform similar operations using a syntax that resembles SQL. However, these are typically tailored to the specific features and data models of the non-relational databases.

Is SQL case-sensitive?

The SQL language itself is not case-sensitive. However, the data contained in the database, such as table names, column names, and data values, may be case-sensitive depending on the database system and its collation settings.

How can I optimize SQL queries for better performance?

Optimizing SQL queries involves several techniques such as selecting only the necessary columns, using proper indexing, avoiding unnecessary joins, and writing efficient WHERE clauses. Analyzing query execution plans and understanding the database schema are also crucial for query optimization.

What is the difference between a primary key and a unique key?

A primary key is a column (or a set of columns) that uniquely identifies each row in a table. A table can have only one primary key. A unique key also ensures that all the values in a column are unique, but unlike a primary key, a table can have multiple unique keys. Additionally, a primary key does not allow NULL values, while a unique key can contain a single NULL value.