HI,
This is a brief documentation of SQL on a beginner level.
As a data scientist, SQL (Structured Query Language) is an essential tool for managing and analyzing data.
Introduction to SQL:
Structured Query Language (SQL) is a programming language designed for managing and manipulating data stored in relational database management systems (RDBMS). SQL is used to create, modify, and retrieve data from databases. SQL is a declarative language, which means that the user declares what they want to do with the data, and the database management system figures out how to do it.
SQL is a standard language, which means that the syntax and structure of the language are the same across all RDBMS.
SQL can be used to:
perform complex calculations
search for specific data
filter and sort data
join multiple tables of data together.
SQL Functions:
SQL functions are pre-defined commands or procedures that are used to manipulate and transform data within an SQL query.
Functions can be used in SELECT, WHERE, and HAVING clauses of an SQL query.
SQL functions are divided into two categories: aggregate functions and scalar functions. Aggregate functions are used to perform calculations on a set of values and return a single result, such as the average or sum of a group of numbers. Scalar functions are used to perform calculations on a single value, such as converting a string to uppercase or lowercase.
Some commonly used SQL functions include:
COUNT: This function returns the number of rows that match a specified condition in a table.
SUM: This function returns the sum of all the values in a column.
AVG: This function returns the average of all the values in a column.
MIN: This function returns the smallest value in a column.
MAX: This function returns the largest value in a column.
CONCAT: This function is used to concatenate two or more strings into a single string.
SUBSTRING: This function is used to extract a portion of a string.
DATE: This function is used to extract the date portion of a date/time value.
UPDATE:This function is used to modify existing data in a table
SQL functions can be combined with other SQL commands to create complex queries that can retrieve and manipulate data in a variety of ways. Understanding SQL functions is an important part of mastering SQL, and can help developers create powerful and efficient SQL queries.
Sorting Data in SQL
(SQL) is used for managing and manipulating relational databases. Two key clauses in SQL that are commonly used together to sort and group data are GROUP BY and ORDER BY.
GROUP BY
used to group the data in a table based on one or more columns.
SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson;
ORDER BY
used to sort the data in a table based on one or more columns.
SELECT *
FROM customers
ORDER BY last_name;
They can be used together
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY total_amount DESC;
SQL conditional statements
Conditionals are used to filter data based on specific conditions and perform edits on that data accordingly.
The most commonly used conditional statements are the WHERE and HAVING clauses.
The WHERE clause is used to filter rows based on a condition.
SELECT *
FROM Employees
WHERE Salary > 50000;
The HAVING clause is used to filter groups based on a condition.
SELECT Salesperson, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Salesperson
HAVING TotalSales > 50000;
Nested select statements
-used to retrieve data that cannot be obtained using a single SQL statement. A nested select statement is a query that is placed inside another query.
-The outer query uses the results of the inner query to perform a more complex operation.(inner query runs first)
`
SELECT
customers.customer_id,
customers.name,
(SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) AS order_count
FROM
customers
`
you can also use Nested statements with WHERE,HAVING,AND.
SELECT Salesperson, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Salesperson
HAVING TotalSales > (SELECT AVG(TotalSales) FROM (SELECT Salesperson, SUM(Amount) AS TotalSales FROM Sales GROUP BY Salesperson) AS T);
Join tables
JOIN is used to combine two or more tables into a single result set based on a related column between them.
There are four types of joins in SQL
1.INNER JOIN
Returns only the rows from both tables where the join condition(primary key in one table, similar key as a foreign key in the other table) is true.
SELECT *
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
to use the other joins
2.FULL OUTER JOIN
Returns all the rows from both tables, including the rows that do not have a match in the other table. If there is no matching row in one of the tables, the result will contain NULL values for the columns of the missing table.
3.LEFT JOIN
Returns all the rows from the left table and the matching rows from the right table. If there is no matching row in the right table, the result will contain NULL values for the columns of the right table.
sql
4.RIGHT JOIN
Returns all the rows from the right table and the matching rows from the left table. If there is no matching row in the left table, the result will contain NULL values for the columns of the left table.
In conclusion,SQL is a powerful feature that allows for complex and efficient queries, subqueries commonly used to filter data or to retrieve aggregate data.
Top comments (0)