SQL Full Course by Intellipaat

Presented by: Multiple Experts

Data: Facts related to an object; examples include age and video data.
Database: Systematic collection of data.
Examples: Telephone directories, contact list on mobiles, power consumption data, social media data.
Importance: Manages large amounts of data, ensures accuracy, ease of data uploading, data security, data integrity.

DBMS: Collection of programs to access, create, manipulate, and delete data in databases.
First DBMS: Developed in 1960 by Charles Bachmann (IDS: Integrated Data Store).
Evolution: DBMS functionalities enhance every year significantly.

SQL: Standard language to deal with relational databases (create, read, update, delete records).
Stands for: Structured Query Language.
Relational Database: Type of database storing data points related to one another.
Flavors of SQL: MySQL, Oracle SQL, Microsoft SQL, Sybase, etc.
SQL Query Example: SELECT * FROM data1 WHERE age < 20;
Applications: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), client-server architecture, three-tier architecture.

Table: Database object comprised of rows and columns.
Fields (Columns): Specific data value, e.g., employee salary.
Records (Rows): Complete information of an entity, e.g., details of an employee named Annie.

NOT NULL: Ensures field contains a value.
DEFAULT: Provides a default value for a field if none specified.
UNIQUE: Ensures all values in a column are different.
PRIMARY KEY: Uniquely identifies each record, combination of NOT NULL and UNIQUE.

Create Table Syntax: CREATE TABLE employee ( e_id INT NOT NULL, e_name VARCHAR(20), e_salary INT, e_age INT, e_gender VARCHAR(20), e_dept VARCHAR(20), PRIMARY KEY (e_id) );
Insert Records Syntax: INSERT INTO employee VALUES (1, 'Sam', 95000, 45, 'Male', 'Operations');

Simple Selection: SELECT e_name FROM employee;
Selecting Multiple Columns: SELECT e_name, e_salary FROM employee;
Selecting with Conditions (WHERE clause): SELECT * FROM employee WHERE e_gender = 'Female';

AND Operator: SELECT * FROM employee WHERE e_gender = 'Male' AND e_age < 30;
OR Operator: SELECT * FROM employee WHERE e_dept = 'Operations' OR e_dept = 'Analytics';
NOT Operator: SELECT * FROM employee WHERE NOT e_gender = 'Female';

LIKE Operator: Extracts records matching a specified pattern. SELECT * FROM employee WHERE e_name LIKE 'J%';
BETWEEN Operator: Selects range of data values. SELECT * FROM employee WHERE e_salary BETWEEN 90000 AND 120000;

Aggregate Functions: MIN, MAX, COUNT, SUM, AVG. SELECT AVG(e_salary) FROM employee;

ORDER BY: Sort data in ascending or descending order. SELECT * FROM employee ORDER BY e_salary DESC;
TOP Clause: Fetch top N records from table. SELECT TOP 3 * FROM employee;

GROUP BY: Aggregate data by one or more columns.
HAVING: Apply conditions to groups. SELECT e_dept, AVG(e_salary) FROM employee GROUP BY e_dept HAVING AVG(e_salary) > 100000;

INNER JOIN: Returns records with matching values in both tables. SELECT a.name, b.salary FROM employees a INNER JOIN salary b ON a.id = b.id;
LEFT JOIN: Returns records from the left table, and matched records from right table. SELECT a.name, b.salary FROM employees a LEFT JOIN salary b ON a.id = b.id;
RIGHT JOIN: Returns records from the right table, and matched records from left table. SELECT a.name, b.salary FROM employees a RIGHT JOIN salary b ON a.id = b.id;
FULL JOIN: Returns all records when there is a match in either table. SELECT a.name, b.salary FROM employees a FULL JOIN salary b ON a.id = b.id;

Temporary Table Creation: CREATE TABLE #temp_table (id INT, name VARCHAR(50));

Definition: A method of manipulating SQL query to gain unauthorized access to a database.
Example: Passing OR '1' = '1'-- to bypass login.
Prevention: Use least privileges, hash passwords, employ third-party authentication.

This completes the key points from the SQL full course lecture by Intellipaat.