Intro to SQL PDF

**INTRO TO SQL** **SQL AND ITS IMPORTANCE:** **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...

**INTRO TO SQL** **SQL AND ITS IMPORTANCE:** **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. **TYPES OF JOINS:** - [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); **USING STRING FUNCTIONS:** **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; **USING SUBQUERIES:** **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);