CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
ALTER TABLE student ADD CONSTRAINT unique_name UNIQUE (name);
CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT CHECK (age >= 18)
);
INSERT INTO student (id) VALUES (1);
CREATE TABLE courses (
course_id INT PRIMARY KEY
);
CREATE TABLE prereq (
course_id INT PRIMARY KEY,
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
ALTER TABLE prereq DROP CONSTRAINT prereq_course_id_fkey;
ALTER TABLE employee ADD CONSTRAINT check_salary CHECK (salary > 30000);
CREATE TABLE enrollment (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE
);
DELETE FROM student WHERE id = 1;
CREATE TABLE composite_key_example (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);
CREATE TABLE sample_table (
id INT PRIMARY KEY,
name VARCHAR(50),
birth_date DATE,
event_time TIME
);
INSERT INTO sample_table VALUES (1, 'John', '2024-01-01', '[Link]');
SELECT CAST('2024-01-01' AS DATE);
SELECT EXTRACT(YEAR FROM birth_date) FROM sample_table;
CREATE TABLE default_example (
id INT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
SELECT NOW() + INTERVAL '1 day';
CREATE TABLE enum_example (
id INT PRIMARY KEY,
status ENUM('Active', 'Inactive', 'Pending')
);
SELECT CAST(10.75 AS INTEGER);
CREATE TABLE boolean_example (
id INT PRIMARY KEY,
is_active BOOLEAN
);
SELECT COALESCE(NULL, 'Default Value');
CREATE INDEX idx_student_name ON student(name);
CREATE INDEX idx_function_based ON student ((LOWER(name)));
CREATE UNIQUE INDEX unique_name_idx ON student(name);
DROP INDEX idx_student_name;
EXPLAIN ANALYZE SELECT * FROM student WHERE name = 'John';
CREATE INDEX idx_composite ON student(first_name, last_name);
CREATE INDEX idx_partial_active ON student(name) WHERE is_active = TRUE;
EXPLAIN ANALYZE SELECT * FROM student WHERE name = 'John';
ALTER TABLE student ADD CONSTRAINT unique_name_alt UNIQUE (name);
CREATE FULLTEXT INDEX idx_fulltext ON student(name);
GRANT SELECT ON student TO user1;
REVOKE INSERT ON student FROM user1;
GRANT ALL PRIVILEGES ON department TO admin;
GRANT SELECT, UPDATE ON student TO user1, user2;
CREATE ROLE manager;
GRANT SELECT, UPDATE ON department TO manager;
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'user1';
CREATE VIEW student_view AS SELECT id, name FROM student WHERE name LIKE 'A%';
GRANT SELECT ON student TO user1 WITH GRANT OPTION;
GRANT manager TO user1;
REVOKE DELETE ON department FROM manager;
