Auto Generate?
In PostgreSQL, Auto Generate usually means automatically generating values for a column, such as an ID.
You can use SERIAL or GENERATED AS IDENTITY.
Example: Using SERIAL
CREATE TABLE student ( id SERIAL PRIMARY KEY, name VARCHAR(50), age INT ); Two ways to auto-generate IDs
SERIAL --> id SERIAL
Identity --> id INT GENERATED ALWAYS AS IDENTITY
Primary Keys
A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null. So, the following two table definitions accept the same data:
CREATE TABLE products ( product_no integer UNIQUE NOT NULL, name text, price numeric ); CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); Primary keys can span more than one column; the syntax is similar to unique constraints:
CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (a, c) ); Foreign Keys
A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products (product_no), quantity integer ); Difference Between Table and View in SQL
In SQL, Table and View are used to organize and access data in a database, but they work differently.
1.Table
A Table is a database object that stores actual data in rows and columns.
CREATE TABLE student ( id SERIAL PRIMARY KEY, name VARCHAR(50), age INT, course VARCHAR(50) ); 2.View
A View is a virtual table created using an SQL query. It displays data from one or more existing tables.
CREATE VIEW student_view AS SELECT name, course FROM student; SELECT * FROM student_view; | Table | View |
|---|---|
| Stores the data | Extractes data from a table |
| Independent database entity | dependent on the table |
can use insert ,update,delete | Mainly used for select |
SQL Commands | DDL, DQL, DML, DCL and TCL Commands
1. DDL - Data Definition Language
- DDL consists of SQL commands that can be used for defining, altering and deleting database structures such as tables, indexes and schemas.
- Commands: CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT.
2. DQL - Data Query Language
- DQL is used to fetch data from the database. The main command is SELECT, which retrieves records based on the query.
- Commands: SELECT.
3. DML - Data Manipulation Language
- DML commands are used to manipulate the data stored in database tables. With DML, you can insert new records, update existing ones, delete unwanted data or retrieve information.
- Commands: INSERT, UPDATE, DELETE.
4. DCL - Data Control Language
- DCL includes commands such as GRANT and REVOKE. These commands are used to control access to data in the database by granting or revoking permissions.
- Commands: GRANT, REVOKE.
5. TCL - Transaction Control Language
- Manages transactions to ensure data integrity and groups DML operations into execution units.
- Commands: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.
Difference between delete,truncate and drop?
| Delete | Truncate | Drop |
|---|---|---|
| DML Command | DDL Command | DDL Command |
| Removes specific rows from a table | Removes all rows from a table | Removes the entire table |
| can use where | cannot use where | cannot use where |
Delete from Table_name where conditions; | Truncate from Table_name; | Drop from Table_name; |
Difference between SQL and NOSQL?
| SQL | NOSQL |
|---|---|
| Relational database | Non-relational database |
| data stored in table | data stored in documents,key-value,graphs.,etc |
| Uses rows and columns | Structure can vary |
| Ex:PostgreSQL,MySQL | Ex:MangoDB,Redis |
Difference between PostgreSQL and MySQL?
| PostgreSQL | MySQL |
|---|---|
| Advanced Relational DB | Relational database |
| More powerful SQL features | simple SQL features |
| Better for complex queries | simpler queries |
Difference between Functions and Stored Procedure?
| Functions | Stored Procedure |
|---|---|
| Used to perform task | task or sequence of operations |
| Must return a value | Does not have to return a value |
can be called in a select statement | Called using CALL |
Constraints
Constraints are used to prevent insertion of invalid data in a table, and ensures the accuracy and reliability of the data in the table.
- NOT NULL - Ensures that a column cannot have a NULL value
- UNIQUE - Ensures that all values in a column are unique
- PRIMARY KEY - Uniquely identifies each row in a table (a combination of a NOT NULL and UNIQUE)
- FOREIGN KEY - Establishes a link between data in two tables, and prevents action that will destroy the link between them
- CHECK - Ensures that the values in a column satisfies a specific condition
- DEFAULT - Sets a default value for a column if no value is specified
- CREATE INDEX - Creates indexes on columns to retrieve data from the database faster
Difference between UNION and UNIONALL?
| UNION | UNIONALL |
|---|---|
| Removes duplicate records | Keeps all records (including duplicates) |
| Generally slower because duplicate rows are removed | Generally faster because duplicates are not removed |
Example: SELECT name FROM employee_2024 UNION SELECT name FROM employee_2025; | Example: SELECT name FROM employee_2024 UNION ALL SELECT name FROM employee_2025; |
Difference between except and intersect?
| except | intersect |
|---|---|
| Returns records present only in the first query | Returns common records from both queries |
| Removes matching records from the first result. | Keeps matching records from both results. |
SELECT name FROM student1 EXCEPT SELECT name FROM student2 | SELECT name FROM student1 INTERSECT SELECT name FROM student2 |
Can we have more than one column as primary key?
Answer:Yes
Composite Key
A composite key is a primary key made from two or more columns that together uniquely identify each record in a table. Individually, these columns may not be unique, but their combined values ensure uniqueness.
CREATE TABLE orders ( order_id INT, product_id INT, quantity INT, price DECIMAL(10,2), PRIMARY KEY (order_id, product_id) ); INSERT INTO orders (order_id, product_id, quantity, price) VALUES (101, 1, 2, 150.00), (101, 2, 1, 250.00), (102, 1, 3, 150.00); Unique Key?
The UNIQUE constraint ensures that all values in a column are unique.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); 
