TEMA 3 - Intro to SQL PDF

INTRO TO SQL SQL (Structured Query Language): it´s used for managing and manipulating relational databases. It provides commands for creating, reading, updating and deleting data in a database. Most relational database management systems support SQL, making it universal for database interaction....

INTRO TO SQL SQL (Structured Query Language): it´s used for managing and manipulating relational databases. It provides commands for creating, reading, updating and deleting data in a database. Most relational database management systems support SQL, making it universal for database interaction. It´s essential for data analysis allowing users to perform queries to extract meaningful insights from datasets. It´s designed to handle large volumes of data in an efficient manner It´s easy to learn and use even with no deep technical knowledge REAL WORLD APPLICATIONS: - Business Intelligence: companies use SQL to generate reports and analyze business performance - Finance: banks and financial institutions use SQL for transaction processing and risk management - Healthcare: medical records and patient data management - E-commerce: inventory management, customer data and sales tracking MAIN DATA TYPES: Data Type Description Example INT Whole numbers 42 FLOAT Floating-point numbers (approximate) 3.14 DOUBLE Double-precision floating-point 2.718281828459 DECIMAL (p,s) Fixed-point numbers with precision and scale 123.45 (DECIMAL(5.2)) VARCHAR (n) Variable-length text up to n chars `John Doe´ (VARCHAR(100)) CHAR (n) Fixed-length text of n chars `A´ (CHAR (1)) TEXT Large amount of text `This is a long text´ DATE Date value (YYYY-MM-DD) `2024-07-09´ TIME Time value (HH:MM) `14:30:00´ DATETIME Date and time value `2024-07-09 14:30:00´ TIMESTAMP Date and time, auto-updated `2024-07-09 14:30:00´ BLOB Binary Large Object [binary data]  BOOLEAN True/False values TRUE or FALSE MAIN STRUCTURES: CREATING TABLES: Table consist of columns (define the type of data) and rows (contain the actual data) Basic syntax of CREATE TABLE: CREATE TABLE TableName ( Column1 DataType1, Column2 DataType2,... ); SELECTING RECORDS: Retrieving data from a table Basic syntax of SELECT query: SELECT column1, column2,... FROM table1 JOIN table2 ON table1.common_column = table2.common_column WHERE condition; INSERTING RECORDS: Insert data into a table. Basic syntax of INSERT query: INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...); UPDATING RECORDS: Updating data into a table. Basic syntax of UPDATE query: UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition; DELETING RECORDS: Delete data from a table. Basic syntax of DELETE query: DELETE FROM table_name WHERE condition; ALTERING A TABLE: Add columns to a table. Basic syntax of ALTER query: ALTER TABLE table_name ADD column_name datatype; DROPING TABLE: Delete a table entirely. Basic syntax of DROP query: DROP TABLE table_name; UNDERSTANDING JOINS IN SQL: JOINS are used in SQL to combine rows from 2 or more tables based on a related column between them. Inner join: it returns only the rows that have matching values in both tables. SYNTAX: SELECT columns FROM table1 INNER JOIN table2 ON table1.common_column=table2.common_column; Left Outer Join: it returns all rows from the left table, and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table. SYNTAX: SELECT columns FROM table1 LEFT JOIN table2  ON table1.common_column=table2.common_column; Right Outer Join: it returns all rows from the right table, and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table. SYNTAX: SELECT columns FROM table1 RIGHT JOIN table2 ON table1.common_column=table2.common_column; USING SIMPLE QUERIES FOR DATA ANALYSIS: These queries cover basic tasks: - Retrieving data - Performing aggregations - Filtering results - Using simple joins SELECT column1, column2,... FROM table_name; SELECT * FROM table_name; SELECT Top X FROM table_name; USING AGGREGATIONS FOR DEEPER ANALYSIS: AGGREGATIONS: used for summarizing data, deriving insights and performing calculations on datasets. SELECT count( * ) FROM table_name; SELECT sum,max,avg,min,…. (column) FROM table_name; FILTERING CONDITIONS: SELECT column1, column2 FROM table_name WHERE condition (); USING OPERATORS: OPERATORS: essential tools for manipulating and retrieving data. They enable to perform various operations on the data (arithmetic calculations, comparisons, logical evaluations and combining multiple conditions in a query). TYPES: Arithmetic: +, -, *, / → SELECT carid, daily_rate, dailyrate- 5 AS discounted_rate FROM cars; Comparison: , =, , !=, LIKE → SELECT * FROM cars WHERE dailyrate > 50; LIKE: used for pattern matching in strings. It´s often used in WHERE clauses to search for a specified pattern in a column. Particularly useful when you need to find records that match a certain pattern rather than an exact match. It´s used with Wildcards like “%” (represents zero, one or multiple characters) and “–” (represents a single character) → SELECT * FROM cars where carmodel like ‘Cam%' Logical operators: AND, OR, NOT → SELECT * FROM rentals WHERE NOT (returndate IS NULL); STRING FUNCTIONS: essential tools for manipulating and transforming text data. They allow you to perform various operations on strings (concatenation, extraction, formatting and searching for specific patterns). 1. Concat: Select concat (CustomerName,' ', CustomerPhone) as FullContact from Customers 2. Substring: SELECT SUBSTRING (customerphone , 1, 3) AS area_code FROM customers; 3. Length: SELECT LENGTH (carmodel) AS model_length FROM cars; 4. Upper and Lower: SELECT UPPER (carmodel) AS model_length FROM cars; 5. Trim, Ltrim, Rtrim: SELECT TRIM (customer_name) AS name FROM customer; 6. Replace: SELECT replace (customer_name,”S”,”s”) AS name FROM customer; 7. Left and Right: Select right (customer_name,2) from customer; SUBQUERIES: queries within another SQL query. They are enclosed in parentheses and can be used in various clauses (SELECT, FROM, WHERE, HAVING). They are powerful tools for breaking down complex queries into simpler parts, making it easier to understand and maintain SQL code. - Subqueries returning 1 row: retrieve the name of the customer who has rented the most expensive car → SELECT CustomerName FROM customers WHERE customerid = ( SELECT customerid FROM rentals ORDER BY totalcost DESC LIMIT 1 ); - Subqueries returning multiple row: retrieve the details of cars that have been rented by customers who have rented more than 2 times → SELECT * FROM cars WHERE carid IN ( SELECT carid FROM rentals WHERE customerid IN ( SELECT customerid FROM rentals GROUP BY customerid HAVING COUNT(*) > 1) ); - In select → SELECT customer_name, (SELECT SUM(total_amount) FROM Orders WHERE Orders.customer_id = Customers.customer_id) AS total_spent FROM Customers; - In where → SELECT first_name, last_name, salary FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees); - Correlated subquery → SELECT first_name, salary FROM Employees e1 WHERE salary > (SELECT AVG(salary) FROM Employees e2 WHERE e2.department = e1.department); - With in → SELECT product_name FROM Products WHERE product_id IN (SELECT product_id FROM Orders);