_edited_edited.png)
SQL stands for Structured Query Language. It is a standardized programming language that is used to manage and manipulate relational databases. SQL is designed for managing data in a relational database management system (RDBMS), which organizes data into tables with rows and columns, and establishes relationships between the tables. It provides a standardized way to interact with databases, allowing you to perform tasks such as querying, inserting, updating, and deleting data.
Here are some key points about SQL:
Querying Data: SQL allows users to retrieve data from a database using SELECT statements. These statements can filter, sort, and aggregate data to meet specific requirements.
Manipulating Data: SQL provides commands for inserting new data into a database (INSERT), updating existing data (UPDATE), and deleting data (DELETE).
Creating and Modifying Database Structures: SQL enables users to create and modify database structures such as tables, indexes, and views using commands like CREATE, ALTER, and DROP.
Data Definition Language (DDL): SQL includes commands for defining and modifying the structure of database objects, such as tables and indexes.
Data Manipulation Language (DML): SQL includes commands for querying and modifying data within tables, such as SELECT, INSERT, UPDATE, and DELETE.
Data Control Language (DCL): SQL includes commands for controlling access to data within the database, such as GRANT and REVOKE.
FUNCTIONS
In the context of SQL, functions are special routines that can accept parameters, perform calculations, and return a single value. They are used to manipulate data, perform calculations, and transform values within SQL queries. SQL functions can be categorized into several types, including:
Scalar Functions: These functions operate on a single value and return a single value. Common scalar functions include:
UPPER() and LOWER(): Convert a string to uppercase or lowercase.
LEN() or LENGTH(): Return the length of a string.
CONCAT(): Concatenate two or more strings.
ROUND() or TRUNC(): Round or truncate a numeric value to a specified number of decimal places.
DATEPART() or EXTRACT(): Extract a specific part of a date (e.g., year, month, day).
2. Aggregate Functions: These functions operate on sets of values and return a single value summarizing the set. Common aggregate functions include:
SUM(): Calculate the sum of a set of values.
AVG(): Calculate the average (mean) of a set of values.
COUNT(): Count the number of rows or non-null values in a set.
MIN(): Find the minimum value in a set.
MAX(): Find the maximum value in a set.
3. Date and Time Functions: These functions are used to manipulate and perform calculations with date and time values. Examples include:
GETDATE() or CURRENT_TIMESTAMP(): Get the current date and time.
DATEADD(): Add a specified interval to a date.
DATEDIFF(): Calculate the difference between two dates.
DATE_TRUNC(): Truncate a date to a specified level of precision (e.g., truncating to the nearest hour or day).
4.String Functions: These functions are used to manipulate string values. Examples include:
SUBSTRING() or SUBSTR(): Extract a substring from a string.
REPLACE(): Replace occurrences of a substring within a string.
CHARINDEX() or INSTR(): Find the position of a substring within a string.
PRACTICE
Here Hospital dataset for practice.
The below queries will explain the functions how to use.
QUERY:
SELECT
CONCAT(first_name, ' ', last_name) AS full_name
FROM patients;
OUTPUT:
Explain the use of functions.
The CONCAT() function adds two or more expressions together.
Explain the output:
the output gives the fist name and last name in same field as a full name
QUERY:
SELECT COUNT(*) AS total_patients
FROM patients
WHERE YEAR(birth_date) = 2010;
OUTPUT:
Explain the use of functions.
The COUNT() function returns the number of rows that matches a specified criterion.
The YEAR() function returns the year part for a given date (a number from 1000 to 9999).
Explain the output:
55 patients had birth year 2010
3.Show the provinces that has more patients identified as 'M' than 'F'. Must only show full province_name.
QUERY:
SELECT pr.province_name
FROM patients AS pa
JOIN province_names AS pr ON pa.province_id = pr.province_id
GROUP BY pr.province_name
HAVING
COUNT( CASE WHEN gender = 'M' THEN 1 END) > COUNT( CASE WHEN gender = 'F' THEN 1 END);
OUTPUT:
Explain the use of functions.
The COUNT() function returns the number of rows that matches a specified criterion.
CASE expression returns the corresponding result that follows the condition.
Explain the output.
Province name given for 'M' than 'F'
4. Show the patient id, first name who is having second highest weight.
QUERY:
SELECT patient_id , first_name, weight
FROM patients
order by weight desc
limit 1 offset 1;
OUTPUT:
Explain the use of functions.
LIMIT clause is used to get a subset of rows generated by a query.
OFFSET is used to skip the number of records from the results.
Explain the output.
Sam is the second highest weight in the patients list.
5. Show the percent of patients that have 'M' as their gender. Round the answer to the nearest hundreth number and in percent form.
QUERY:
SELECT CONCAT(
ROUND(
(
SELECT COUNT(*)
FROM patients
WHERE gender = 'M'
) / CAST(COUNT(*) as float),
4
) * 100,
'%'
) as percent_of_male_patients
FROM patients;
OUTPUT:
Explain the use of functions.
Round or truncate a numeric value to a specified number of decimal places.
Explain the output.
They are 54.48 percentage of male patients in the hospital.
REFERENCE: