Microsoft Windows [Version 10.0.19045.5854] (c) Microsoft Corporation. All rights reserved.
C:\Users\Minfy>psql -U postgres Password for user postgres: psql (17.5) WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. Type "help" for help.
postgres=# CREATE USER university_admin WITH PASSWORD 'Ashim2312@ar' postgres-# CREATE USER university_admin WITH PASSWORD 'Ashim2312@ar'; ERROR: syntax error at or near "CREATE" LINE 2: CREATE USER university_admin WITH PASSWORD 'Ashim2312@ar'; ^ postgres=# CREATE USER university_admin WITH PASSWORD 'Ashim2312@ar'; ERROR: role "university_admin" already exists postgres=# CREATE DATABASE university_db OWNER university_admin; CREATE DATABASE postgres=# GRANT ALL PRIVILEGES ON DATABASE university_db TO university_admin; GRANT postgres=# \q
C:\Users\Minfy>psql -U university_admin -d university_db; Password for user university_admin: psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: database "university_db;" does not exist
C:\Users\Minfy>psql -U university_admin -d university_db Password for user university_admin: psql (17.5) WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. Type "help" for help.
university_db=> \l List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges ---------------+------------------+----------+-----------------+--------------------+--------------------+--------+-----------+--------------------------------------- postgres | postgres | UTF8 | libc | English_India.1252 | English_India.1252 | | | template0 | postgres | UTF8 | libc | English_India.1252 | English_India.1252 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | libc | English_India.1252 | English_India.1252 | | | =c/postgres + | | | | | | | | postgres=CTc/postgres university_db | university_admin | UTF8 | libc | English_India.1252 | English_India.1252 | | | =Tc/university_admin + | | | | | | | | university_admin=CTc/university_admin (4 rows)
university_db=> CREATE TABLE students( university_db(> student_id INTEGER, university_db(> first_name VARCHAR(50), university_db(> last_name VARCHAR(50), university_db(> email VARCHAR(100), university_db(> date_of_birth DATE university_db(> ); CREATE TABLE university_db=> \d List of relations Schema | Name | Type | Owner --------+----------+-------+------------------ public | students | table | university_admin (1 row)
university_db=> ALTER TABLE students ADD COLUMN enrollment_date DATE; ALTER TABLE university_db=> ALTER TABLE students DROP COLUMN enrollment_date; ALTER TABLE university_db=> ALTER TABLE students ALTER COLUMN email TYPE VARCHAR(150); ALTER TABLE university_db=> ALTER TABLE students RENAME COLUMN date_of_birth TO dob; ALTER TABLE university_db=> ALTER TABLE students ADD CONSTRAINT unique_email UNIQUE (email); ALTER TABLE university_db=> ALTER TABLE students RENAME TO learners; ALTER TABLE university_db=> ALTER TABLE learners RENAME TO students; ALTER TABLE university_db=> ALTER TABLE students ADD COLUMN phone_number VARCHAR(20); ALTER TABLE university_db=> ALTER TABLE students DROP COLUMN phone_number; ALTER TABLE university_db=> INSERT INTO students (student_id, first_name, last_name, email, dob) university_db-> VALUES (1, 'Alice', 'Smith', 'alice.smith@example.com', '2003-05-15'); INSERT 0 1 university_db=> INSERT INTO students (student_id, first_name, last_name, email, dob) university_db-> VALUES (2, 'Bob', 'Johnson', 'bob.johnson@example.com', '2002-08-22'), university_db-> (3, 'Charlie', 'Brown', 'charlie.brown@example.com', '2003-01-10'); INSERT 0 2 university_db=> INSERT INTO students (student_id, first_name, last_name, email, dob) university_db-> VALUES (4, 'Dolon', 'Dey', 'dolon.dey@example.com', '2002-10-30'), university_db-> VALUES (5, 'Dheemanth', 'M.M.', 'dheemanth.mm@example.com', '2003-04-02'); ERROR: syntax error at or near "VALUES" LINE 3: VALUES (5, 'Dheemanth', 'M.M.', 'dheemanth.mm@example.com', ... ^ university_db=> INSERT INTO students (student_id, first_name, last_name, email, dob) university_db-> VALUES (4, 'Dolon', 'Dey', 'dolon.dey@example.com', '2002-10-30'), university_db-> (5, 'Dheemanth', 'M.M.', 'dheemanth.mm@example.com', '2003-04-02'); INSERT 0 2 university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 (5 rows)
university_db=> SELECT first_name, last_name, email FROM students; first_name | last_name | email ------------+-----------+--------------------------- Alice | Smith | alice.smith@example.com Bob | Johnson | bob.johnson@example.com Charlie | Brown | charlie.brown@example.com Dolon | Dey | dolon.dey@example.com Dheemanth | M.M. | dheemanth.mm@example.com (5 rows)
university_db=> SELECT * FROM students WHERE student_id = 1; student_id | first_name | last_name | email | dob ------------+------------+-----------+-------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 (1 row)
university_db=> SELECT * FROM students WHERE dob >= '2003-01-01'; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 (3 rows)
university_db=> SELECT * FROM students WHERE dob BETWEEN '2002-01-01' AND '2002-12-31'; student_id | first_name | last_name | email | dob ------------+------------+-----------+-------------------------+------------ 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 (2 rows)
university_db=> SELECT * FROM students WHERE first_name LIKE 'A%'; student_id | first_name | last_name | email | dob ------------+------------+-----------+-------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 (1 row)
university_db=> SELECT * FROM students WHERE email ILIKE '%.com'; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 (5 rows)
university_db=> SELECT * FROM students WHERE dob BETWEEN '1995-01-01' AND '2005-12-31'; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 (5 rows)
university_db=> SELECT * FROM students WHERE first_name = 'Alice' AND last_name = 'Smith'; student_id | first_name | last_name | email | dob ------------+------------+-----------+-------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 (1 row)
university_db=> SELECT * FROM students WHERE student_id = 1 OR student_id = 3; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 (2 rows)
university_db=> SELECT * FROM students WHERE student_id IN (1, 3, 5); student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 (3 rows)
university_db=> INSERT INTO students (student_id, first_name, last_name, dob) university_db-> VALUES (6, 'Diana', 'Prince', '2001-11-01'); INSERT 0 1 university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 6 | Diana | Prince | | 2001-11-01 (6 rows)
university_db=> SELECT * FROM students WHERE email is NULL; student_id | first_name | last_name | email | dob ------------+------------+-----------+-------+------------ 6 | Diana | Prince | | 2001-11-01 (1 row)
university_db=> SELECT * FROM students WHERE email is NOT NULL; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 (5 rows)
university_db=> SELECT * FROM students WHERE student_id = 2; student_id | first_name | last_name | email | dob ------------+------------+-----------+-------------------------+------------ 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 (1 row)
university_db=> SELECT * FROM students WHERE dob < '2003-01-01'; student_id | first_name | last_name | email | dob ------------+------------+-----------+-------------------------+------------ 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 6 | Diana | Prince | | 2001-11-01 (3 rows)
university_db=> SELECT * FROM students WHERE first_name LIKE 'A%' OR first_name LIKE 'B%'; student_id | first_name | last_name | email | dob ------------+------------+-----------+-------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 (2 rows)
university_db=> SELECT * FROM students WHERE first_name LIKE 'B%' OR first_name LIKE 'C%'; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 (2 rows)
university_db=> SELECT * FROM students WHERE email ILIKE '%example.com'; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 (5 rows)
university_db=> SELECT * FROM students WHERE email is NOT NULL; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 2 | Bob | Johnson | bob.johnson@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 (5 rows)
university_db=> SELECT * FROM students WHERE email is NULL; student_id | first_name | last_name | email | dob ------------+------------+-----------+-------+------------ 6 | Diana | Prince | | 2001-11-01 (1 row)
university_db=> UPDATE students university_db-> SET email = 'alices@example.net' university_db-> WHERE student_id = 1; UPDATE 1 university_db=> UPDATE students university_db-> SET first_name = 'Robert', email = 'robert.j@example.com' university_db-> WHERE student_id = 2; UPDATE 1 university_db=> SELECT * FROM students WHERE student_id IN (1,2); student_id | first_name | last_name | email | dob ------------+------------+-----------+----------------------+------------ 1 | Alice | Smith | alices@example.net | 2003-05-15 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 (2 rows)
university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 6 | Diana | Prince | | 2001-11-01 1 | Alice | Smith | alices@example.net | 2003-05-15 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 (6 rows)
university_db=> UPDATE students university_db-> SET dob = '2003-02-15', university_db-> WHERE student_id = 3; ERROR: syntax error at or near "WHERE" LINE 3: WHERE student_id = 3; ^ university_db=> UPDATE students university_db-> SET dob = '2003-02-15' university_db-> WHERE student_id = 3; UPDATE 1 university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 6 | Diana | Prince | | 2001-11-01 1 | Alice | Smith | alices@example.net | 2003-05-15 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 (6 rows)
university_db=> UPDATE students university_db-> SET email = 'diana.prince@example.org' university_db-> WHERE student_id = 6; UPDATE 1 university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 1 | Alice | Smith | alices@example.net | 2003-05-15 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 6 | Diana | Prince | diana.prince@example.org | 2001-11-01 (6 rows)
university_db=> INSERT INTO students (student_id, first_name, last_name, email, dob) university_db-> VALUES (99, 'Temp', 'User', 'temp@example.com', '2000-01-01'); INSERT 0 1 university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 1 | Alice | Smith | alices@example.net | 2003-05-15 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 6 | Diana | Prince | diana.prince@example.org | 2001-11-01 99 | Temp | User | temp@example.com | 2000-01-01 (7 rows)
university_db=> DELETE FROM students WHERE student_id = 99; DELETE 1 university_db=> SELECT * FROM students WHERE student_id = 99; student_id | first_name | last_name | email | dob ------------+------------+-----------+-------+----- (0 rows)
university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 1 | Alice | Smith | alices@example.net | 2003-05-15 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 6 | Diana | Prince | diana.prince@example.org | 2001-11-01 (6 rows)
university_db=> SELECT * FROM students ORDER BY last_name ASC; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 6 | Diana | Prince | diana.prince@example.org | 2001-11-01 1 | Alice | Smith | alices@example.net | 2003-05-15 (6 rows)
university_db=> SELECT * FROM students ORDER BY dob DESC; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alices@example.net | 2003-05-15 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 6 | Diana | Prince | diana.prince@example.org | 2001-11-01 (6 rows)
university_db=> SELECT * FROM students ORDER BY last_name, first_name; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 6 | Diana | Prince | diana.prince@example.org | 2001-11-01 1 | Alice | Smith | alices@example.net | 2003-05-15 (6 rows)
university_db=> SELECT * FROM students ORDER BY dob ASC; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 6 | Diana | Prince | diana.prince@example.org | 2001-11-01 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 1 | Alice | Smith | alices@example.net | 2003-05-15 (6 rows)
university_db=> SELECT * FROM students ORDER BY last_name DESC OR first_name ASC; ERROR: syntax error at or near "OR" LINE 1: SELECT * FROM students ORDER BY last_name DESC OR first_name... ^ university_db=> SELECT * FROM students ORDER BY last_name DESC, first_name ASC; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alices@example.net | 2003-05-15 6 | Diana | Prince | diana.prince@example.org | 2001-11-01 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 (6 rows)
university_db=> SELECT * FROM students ORDER BY dob DESC LIMIT 3; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 1 | Alice | Smith | alices@example.net | 2003-05-15 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 (3 rows)
university_db=> SELECT * FROM students ORDER BY student_id LIMIT 2 OFFSET 2; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 (2 rows)
university_db=> SELECT * FROM students ORDER BY dob ASC LIMIT 2; student_id | first_name | last_name | email | dob ------------+------------+-----------+--------------------------+------------ 6 | Diana | Prince | diana.prince@example.org | 2001-11-01 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 (2 rows)
university_db=> SELECT * FROM students ORDER BY student_id LIMIT 2 OFFSET 1; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 (2 rows)
Brown Dey Johnson M.M. Prince Smith (6 rows)
university_db=> SELECT DISTINCT last_name, first_name FROM students; last_name | first_name -----------+------------ Prince | Diana Smith | Alice Johnson | Robert Brown | Charlie M.M. | Dheemanth Dey | Dolon (6 rows)
6
(1 row)
6
(1 row)
university_db=> SELECT COUNT(DISTINCT last_name) AS unique_last_names FROM students; unique_last_names
6
(1 row)
2001-11-01 (1 row)
2003-05-15 (1 row)
6
(1 row)
2001-11-01 (1 row)
university_db=> SELECT COUNT(DISTINCT last_name) AS unique_last_names FROM students; unique_last_names
6
(1 row)
university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob ------------+------------+-----------+---------------------------+------------ 4 | Dolon | Dey | dolon.dey@example.com | 2002-10-30 5 | Dheemanth | M.M. | dheemanth.mm@example.com | 2003-04-02 1 | Alice | Smith | alices@example.net | 2003-05-15 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 3 | Charlie | Brown | charlie.brown@example.com | 2003-02-15 6 | Diana | Prince | diana.prince@example.org | 2001-11-01 (6 rows)
university_db=> SELECT last_name, COUNT(*) AS number_of_students university_db-> FROM students university_db-> GROUP BY last_name university_db-> ORDER BY number_of_students DESC; last_name | number_of_students -----------+-------------------- M.M. | 1 Smith | 1 Johnson | 1 Dey | 1 Prince | 1 Brown | 1 (6 rows)
university_db=> SELECT last_name, COUNT() AS number_of_students university_db-> FROM students university_db-> GROUP BY last_name university_db-> HAVING COUNT() > 1 university_db-> ORDER BY number_of_students DESC; last_name | number_of_students -----------+-------------------- (0 rows)
university_db=> SELECT first_name, COUNT(*) AS student_count university_db-> FROM students university_db-> GROUP BY first_name university_db-> ORDER BY student_count DESC, first_name; first_name | student_count ------------+--------------- Alice | 1 Charlie | 1 Dheemanth | 1 Diana | 1 Dolon | 1 Robert | 1 (6 rows)
university_db=> SELECT EXTRACT(YEAR FROM dob) AS birth_year, COUNT(*) AS students_in_year university_db-> FROM students university_db-> WHERE dob IS NOT NULL university_db-> GROUP BY birth_year university_db-> ORDER BY birth_year; birth_year | students_in_year ------------+------------------ 2001 | 1 2002 | 2 2003 | 3 (3 rows)
university_db=> DROP TABLE students; DROP TABLE university_db=> CREATE TABLE students ( university_db(> student_id INTEGER, university_db(> first_name VARCHAR(50) NOT NULL, university_db(> last_name VARCHAR(50) NOT NULL, university_db(> email VARCHAR(100) UNIQUE, university_db(> dob DATE, university_db(> enrollment_status VARCHAR(10) CHECK (enrollment_status IN ('enrolled', 'graduated', 'dropped'))); CREATE TABLE university_db=> INSERT INTO students (student_id, last_name, email, dob) VALUES (1, 'Test', 'test@test.com', '2000-01-01'); ERROR: null value in column "first_name" of relation "students" violates not-null constraint DETAIL: Failing row contains (1, null, Test, test@test.com, 2000-01-01, null). university_db=> INSERT INTO students (student_id, first_name, last_name, email, dob) university_db-> VALUES (1, 'Test', 'User', 'test@test.com', '2000-01-01'); INSERT 0 1 university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob | enrollment_status ------------+------------+-----------+---------------+------------+------------------- 1 | Test | User | test@test.com | 2000-01-01 | (1 row)
university_db=> INSERT INTO students (student_id, first_name, last_name, email, dob) university_db-> VALUES (2, 'Another', 'User', 'test@test.com', '2001-01-01'); ERROR: duplicate key value violates unique constraint "students_email_key" DETAIL: Key (email)=(test@test.com) already exists. university_db=> INSERT INTO students (student_id, first_name, last_name, email, dob, enrollment_status) university_db-> VALUES (2, 'Another', 'User', 'another@test.com', '2001-01-01', 'pending'); ERROR: new row for relation "students" violates check constraint "students_enrollment_status_check" DETAIL: Failing row contains (2, Another, User, another@test.com, 2001-01-01, pending). university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob | enrollment_status ------------+------------+-----------+---------------+------------+------------------- 1 | Test | User | test@test.com | 2000-01-01 | (1 row) university_db=> DROP TABLE IF EXISTS students; DROP TABLE university_db=> CREATE TABLE students ( university_db(> student_id SERIAL PRIMARY KEY, university_db(> first_name VARCHAR(50) NOT NULL, university_db(> last_name VARCHAR(50) NOT NULL, university_db(> email VARCHAR(100) UNIQUE, university_db(> dob DATE, university_db(> enrollment_status VARCHAR(20) CHECK (enrollment_status IN ('enrolled', 'graduated', 'dropped', 'pending'))); CREATE TABLE university_db=> INSERT INTO students (first_name, last_name, email, dob, enrollment_status) university_db-> VALUES ('Alice', 'Smith', 'alice.smith@example.com', '2003-05-15', 'enrolled'); INSERT 0 1 university_db=> INSERT INTO students (first_name, last_name, email, dob, enrollment_status) university_db-> VALUES ('Robert', 'Johnson', 'robert.j@example.com', '2002-08-22', 'enrolled'); INSERT 0 1 university_db=> INSERT INTO students (first_name, last_name, email, dob, enrollment_status) university_db-> VALUES ('Charlie', 'Brown', 'charlie.brown@example.com', '2003-01-10', 'pending'); INSERT 0 1 university_db=> SELECT * FROM students; student_id | first_name | last_name | email | dob | enrollment_status ------------+------------+-----------+---------------------------+------------+------------------- 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 | enrolled 2 | Robert | Johnson | robert.j@example.com | 2002-08-22 | enrolled 3 | Charlie | Brown | charlie.brown@example.com | 2003-01-10 | pending (3 rows) university_db=> CREATE TABLE courses ( university_db(> course_id SERIAL PRIMARY KEY, university_db(> course_name VARCHAR(100) NOT NULL UNIQUE, university_db(> credits INTEGER CHECK (credits > 0 AND credits < 10)); CREATE TABLE university_db=> INSERT INTO courses (course_name, credits) university_db-> VALUES ('Introduction to SQL', 3); INSERT 0 1 university_db=> INSERT INTO courses (course_name, credits) university_db-> VALUES ('Database Design', 4); INSERT 0 1 university_db=> INSERT INTO courses (course_name, credits) university_db-> VALUES ('Web Development', 3); INSERT 0 1 university_db=> INSERT INTO courses (course_name, credits) university_db-> VALUES ('Data Structures', 4); INSERT 0 1 university_db=> SELECT * FROM courses; course_id | course_name | credits -----------+---------------------+--------- 1 | Introduction to SQL | 3 2 | Database Design | 4 3 | Web Development | 3 4 | Data Structures | 4 (4 rows) university_db=> CREATE TABLE enrollments ( university_db(> enrollment_id SERIAL PRIMARY KEY, university_db(> student_id INTEGER NOT NULL, university_db(> course_id INTEGER NOT NULL, university_db(> enrollment_date DATE DEFAULT CURRENT_DATE, university_db(> grade CHAR(1) CHECK (grade IN ('A', 'B', 'C', 'D', 'F', 'W', NULL)), university_db(> CONSTRAINT fk_student university_db(> FOREIGN KEY (student_id) university_db(> REFERENCES students(student_id) university_db(> ON DELETE CASCADE, university_db(> CONSTRAINT fk_course university_db(> FOREIGN KEY (course_id) university_db(> REFERENCES courses(course_id) university_db(> ON DELETE RESTRICT, university_db(> UNIQUE (student_id, course_id) university_db(> ); CREATE TABLE university_db=> SELECT * FROM enrollments; enrollment_id | student_id | course_id | enrollment_date | grade ---------------+------------+-----------+-----------------+------- (0 rows) university_db=> INSERT INTO enrollments (student_id, course_id, grade) university_db-> VALUES (999, 1, 'A'); ERROR: insert or update on table "enrollments" violates foreign key constraint "fk_student" DETAIL: Key (student_id)=(999) is not present in table "students". university_db=> INSERT INTO enrollments (student_id, course_id, grade) university_db-> VALUES (1, 999, 'A'); ERROR: insert or update on table "enrollments" violates foreign key constraint "fk_course" DETAIL: Key (course_id)=(999) is not present in table "courses". university_db=> INSERT INTO enrollments (student_id, course_id, grade) university_db-> VALUES (1, 1, 'A'); INSERT 0 1 university_db=> INSERT INTO enrollments (student_id, course_id) university_db-> VALUES (1, 2); INSERT 0 1 university_db=> INSERT INTO enrollments (student_id, course_id, grade) university_db-> VALUES (2, 1, 'B'); INSERT 0 1 university_db=> SELECT * FROM students WHERE student_id = 1; student_id | first_name | last_name | email | dob | enrollment_status ------------+------------+-----------+-------------------------+------------+------------------- 1 | Alice | Smith | alice.smith@example.com | 2003-05-15 | enrolled (1 row)
university_db=> SELECT * FROM enrollments WHERE student_id = 1; enrollment_id | student_id | course_id | enrollment_date | grade ---------------+------------+-----------+-----------------+------- 3 | 1 | 1 | 2025-05-30 | A 4 | 1 | 2 | 2025-05-30 | (2 rows)
university_db=> DELETE FROM students WHERE student_id = 1; DELETE 1 university_db=> SELECT * FROM students WHERE student_id = 1; student_id | first_name | last_name | email | dob | enrollment_status ------------+------------+-----------+-------+-----+------------------- (0 rows)
university_db=> SELECT * FROM enrollments WHERE student_id = 1; enrollment_id | student_id | course_id | enrollment_date | grade ---------------+------------+-----------+-----------------+------- (0 rows)
university_db=> DELETE FROM courses WHERE course_id = 1; ERROR: update or delete on table "courses" violates foreign key constraint "fk_course" on table "enrollments" DETAIL: Key (course_id)=(1) is still referenced from table "enrollments". university_db=> INSERT INTO students (student_id, first_name, last_name, email, dob) university_db-> VALUES (1, 'Alice', 'Smith', 'alices@example.net', '2003-05-15'); INSERT 0 1 university_db=> INSERT INTO enrollments (student_id, course_id, grade) university_db-> VALUES (1, 1, 'A'); INSERT 0 1 university_db=> INSERT INTO enrollments (student_id, course_id) university_db-> VALUES (1, 2); INSERT 0 1