SQL Essentials / learning workspace
Your technical depth starts here.
MODULE 1 / 6
Module 1 – SQL Fundamentals · SQL Command Categories
Module 1 – SQL Fundamentals
SQL Fundamentals
- SQL stands for Structured Query Language.
- SQL is used to communicate with relational database systems.
- SQL can be used to:
- Create databases.
- Create tables.
- Insert data.
- Retrieve data.
- Update data.
- Delete data.
- Control permissions.
- Manage transactions.
- SQL is declarative.
- In declarative programming, the programmer specifies what result is required rather than every implementation step.
- The database optimizer decides an efficient execution plan.
- SQL is standardized by ANSI and ISO.
- MySQL supports standard SQL plus MySQL-specific features.
- ANSI SQL refers to SQL features standardized across database systems.
- SQL keywords are generally written in uppercase for readability.
- SQL statements generally end with a semicolon.
- A database contains tables, views, indexes, procedures, functions, and other objects.
- A table contains rows and columns.
- A row is also called a record or tuple.
- A column is also called an attribute or field.
- A database schema defines the structure of database objects.
- A database instance represents the data stored at a particular moment.
- A NULL value represents missing, unknown, or inapplicable data.
- NULL is not the same as zero.
- NULL is not the same as an empty string.
- SQL uses three-valued logic:
- TRUE.
- FALSE.
- UNKNOWN.
- Any comparison with NULL normally produces UNKNOWN.
- Use IS NULL to check for NULL.
- Use IS NOT NULL to check for a non-NULL value.
- SQL comments can be written using --.
sql
-- This is a single-line comment
26. MySQL also supports block comments. sql
/* This is
a block comment */
27. SQL identifiers include database names, table names, column names, and aliases.
28. Avoid using reserved keywords as table or column names.
29. A table’s degree is the number of columns.
30. A table’s cardinality is the number of rows.
The booklet’s SQL worksheets specifically test NULL, table rows and columns, WHERE, GROUP BY, DISTINCT, UNION, transactions, and database structure.Complete-Placement-Preparation-Booklet-DSA-Core-Subjects-Interview-Guide.pdfSQL Command Categories
- DDL means Data Definition Language.
- DDL defines or changes database structure.
- Common DDL commands:
- CREATE.
- ALTER.
- DROP.
- TRUNCATE.
- CREATE DATABASE creates a database.
sql
CREATE DATABASE company;
35. USE selects a database. sql
USE company;
36. CREATE TABLE creates a table. sql
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
salary DECIMAL(10, 2),
department_id INT
);
37. ALTER TABLE modifies the structure of an existing table.
38. Add a column: sql
ALTER TABLE employees
ADD email VARCHAR(100);
39. Modify a column in MySQL: sql
ALTER TABLE employees
MODIFY employee_name VARCHAR(100);
40. Rename a column in MySQL: sql
ALTER TABLE employees
RENAME COLUMN employee_name TO full_name;
41. Drop a column: sql
ALTER TABLE employees
DROP COLUMN email;
42. DROP TABLE removes both table structure and table data. sql
DROP TABLE employees;
43. TRUNCATE TABLE removes all rows while retaining the table structure. sql
TRUNCATE TABLE employees;
44. DML means Data Manipulation Language.
45. Common DML commands:
* INSERT.
* UPDATE.
* DELETE.
46. INSERT adds rows.
47. UPDATE modifies existing rows.
48. DELETE removes rows.
49. DQL means Data Query Language.
50. SELECT is the primary DQL command.
51. DCL means Data Control Language.
52. GRANT gives privileges.
53. REVOKE removes privileges.
54. TCL means Transaction Control Language.
55. Common TCL commands:
* COMMIT.
* ROLLBACK.
* SAVEPOINT.Table Creation and Constraints
- A constraint is a rule applied to table data.
- PRIMARY KEY uniquely identifies each row.
- A primary-key column cannot contain NULL.
- A table can have only one primary-key constraint.
- A primary key may contain multiple columns.
- A multi-column primary key is called a composite primary key.
- FOREIGN KEY creates a relationship between two tables.
- A foreign key references a primary key or candidate key in another table.
- A foreign key enforces referential integrity.
- UNIQUE prevents duplicate values.
- NOT NULL prevents missing values.
- DEFAULT provides a value when no value is supplied.
- CHECK restricts allowed values.
- Example:
sql
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50) UNIQUE NOT NULL
);
70. Example with a foreign key: sql
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50) NOT NULL,
salary DECIMAL(10, 2) CHECK (salary >= 0),
department_id INT,
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
71. A foreign key may contain NULL if the relationship is optional.
72. A foreign key cannot contain a non-NULL value that does not exist in the referenced table.
73. ON DELETE CASCADE deletes dependent rows automatically.
74. ON UPDATE CASCADE updates dependent foreign-key values automatically.
75. ON DELETE SET NULL sets the foreign key to NULL, provided the column allows NULL.
76. ON DELETE RESTRICT prevents deletion of a referenced row if dependent rows exist.Data Types
- INT stores integer values.
- DECIMAL(p, s) stores exact fixed-point numbers.
- In DECIMAL(10, 2), 10 is the total number of digits and 2 is the number of digits after the decimal point.
- FLOAT and DOUBLE store approximate floating-point values.
- Use DECIMAL for money where exact precision is important.
- CHAR(n) stores fixed-length strings.
- VARCHAR(n) stores variable-length strings up to length n.
- CHAR may be useful when values have fixed length.
- VARCHAR is generally suitable for names, email addresses, and variable-length text.
- TEXT stores larger text values.
- DATE stores date values.
- TIME stores time values.
- DATETIME stores date and time.
- TIMESTAMP stores a timestamp and may have automatic update behavior depending on configuration.
- BOOLEAN in MySQL is commonly treated as a numeric type with values such as 0 and 1.
- Choose data types according to range, precision, storage, and application requirements.
INSERT, SELECT, UPDATE, and DELETE
- Insert one row:
sql
INSERT INTO departments (department_id, department_name)
VALUES (10, 'IT');
94. Insert multiple rows: sql
INSERT INTO departments (department_id, department_name)
VALUES
(20, 'HR'),
(30, 'Sales'),
(40, 'Finance');
95. Select all columns: sql
SELECT *
FROM departments;
96. Select specific columns: sql
SELECT department_id, department_name
FROM departments;
97. Use an alias for a column: sql
SELECT department_name AS department
FROM departments;
98. Filter rows with WHERE: sql
SELECT *
FROM employees
WHERE salary > 50000;
99. Update rows: sql
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 10;
100. Always use a proper WHERE condition in UPDATE unless every row should change.
101. Delete selected rows: sql
DELETE FROM employees
WHERE employee_id = 101;
102. Omitting WHERE from DELETE may remove every row.
103. SELECT does not permanently modify data.
104. INSERT, UPDATE, and DELETE modify table data.Operators and Filtering
- Comparison operators include:
- =.
- <> or !=.
-
.
- <.
-
=.
- <=.
- AND requires all conditions to be true.
sql
SELECT *
FROM employees
WHERE salary > 40000
AND department_id = 10;
107. OR requires at least one condition to be true. sql
SELECT *
FROM employees
WHERE department_id = 10
OR department_id = 20;
108. Use parentheses to control logical precedence. sql
SELECT *
FROM employees
WHERE department_id = 10
AND (salary > 40000 OR employee_name = 'Amit');
109. IN checks membership in a list. sql
SELECT *
FROM employees
WHERE department_id IN (10, 20, 30);
110. NOT IN excludes values from a list.
111. BETWEEN checks an inclusive range. sql
SELECT *
FROM employees
WHERE salary BETWEEN 30000 AND 60000;
112. BETWEEN includes both boundary values.
113. LIKE performs pattern matching.
114. % represents zero or more characters.
115. _ represents exactly one character. sql
SELECT *
FROM employees
WHERE employee_name LIKE 'A%';
This returns names beginning with A. sql
SELECT *
FROM employees
WHERE employee_name LIKE '_mit';
This may match Amit.
116. IS NULL checks for missing values. sql
SELECT *
FROM employees
WHERE department_id IS NULL;
117. IS NOT NULL checks for available values.
118. CASE performs conditional logic. sql
SELECT employee_name,
CASE
WHEN salary >= 80000 THEN 'High'
WHEN salary >= 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM employees;SQL Execution Order
The logical processing order of a typical SQL query is:
- FROM.
- JOIN.
- WHERE.
- GROUP BY.
- HAVING.
- SELECT.
- DISTINCT.
- ORDER BY.
- LIMIT.
This explains why
- A SELECT alias may not be available in WHERE.
- Aggregate functions are generally filtered using HAVING.
- WHERE filters rows before grouping.
- HAVING filters groups after grouping.