Postgresql

Question 1

1 What command is used to create a new PostgreSQL role

Answer 1

Use CREATE ROLE role_name optionally adding attributes such as LOGIN or CREATEDB

Question 2

2 How do you create a role that can log in

Answer 2

Use CREATE ROLE role_name LOGIN PASSWORD ‘password’

Question 3

3 What is the difference between a role and a user in PostgreSQL

Answer 3

There is no functional difference a user is simply a role with the LOGIN privilege

Question 4

4 How do you list all roles in a PostgreSQL database

Answer 4

Use the psql command \du to display all roles

Question 5

5 How is a new database created in PostgreSQL

Answer 5

Use CREATE DATABASE db_name

Question 6

6 How do you assign a database owner at creation time

Answer 6

Use CREATE DATABASE db_name OWNER role_name

Question 7

7 How do you connect to a specific database using psql

Answer 7

Use \c db_name to switch databases

Question 8

8 What command deletes a PostgreSQL database

Answer 8

Use DROP DATABASE db_name

Question 9

9 What is a schema in PostgreSQL

Answer 9

A schema is a logical namespace that organizes database objects

Question 10

10 How do you create a new schema

Answer 10

Use CREATE SCHEMA schema_name

Question 11

11 How do you create a table in PostgreSQL

Answer 11

Use CREATE TABLE table_name (column_name data_type)

Question 12

12 How do you specify a primary key in a table definition

Answer 12

Declare PRIMARY KEY (column_name) or append PRIMARY KEY to the column

Question 13

13 What is the purpose of the SERIAL data type

Answer 13

It automatically generates sequential integers using a sequence

Question 14

14 How do you insert a row into a table

Answer 14

Use INSERT INTO table_name (columns) VALUES (values)

Question 15

15 How do you insert multiple rows in one statement

Answer 15

Use INSERT INTO table_name VALUES (row1)

Question 16

16 How do you insert data from another table

Answer 16

Use INSERT INTO table_name SELECT columns FROM other_table

Question 17

17 How do you prevent NULL values in a column

Answer 17

Add the NOT NULL constraint to the column

Question 18

18 What does the UNIQUE constraint enforce

Answer 18

It ensures all values in a column or column set are distinct

Question 19

19 How do you define a foreign key

Answer 19

Use FOREIGN KEY (column) REFERENCES other_table(column)

Question 20

20 How do you grant privileges on a table

Answer 20

Use GRANT privilege ON table_name TO role_name

Question 21

21 How do you revoke privileges from a role

Answer 21

Use REVOKE privilege ON object FROM role_name

Question 22

22 What privilege allows a role to create tables in a schema

Answer 22

The CREATE privilege on the schema

Question 23

23 How do you set a role as the default owner of new objects

Answer 23

Create objects while connected as that role or use ALTER DEFAULT PRIVILEGES

Question 24

24 How do you change the owner of a table

Answer 24

Use ALTER TABLE table_name OWNER TO role_name

Question 25

25 What command lists all tables in the current schema using psql

Answer 25

Use \dt

Question 26

26 Insert a new row into a table named students with values 1 Alice Physics

Answer 26

INSERT INTO students VALUES (1

Question 27

27 Display all rows and columns from a table named courses

Answer 27

SELECT * FROM courses

Question 28

28 Insert a row into employees specifying only id and name with values 5 Jordan

Answer 28

INSERT INTO employees (id

Question 29

29 Display only the email column from the users table

Answer 29

SELECT email FROM users

Question 30

30 Insert three rows into departments with values 1 Math 2 CS 3 Biology

Answer 30

INSERT INTO departments VALUES (1

Question 31

31 Display all rows from orders where status is pending

Answer 31

SELECT * FROM orders WHERE status = 'pending'

Question 32

32 Insert rows into books using values selected from new_books

Answer 32

INSERT INTO books SELECT * FROM new_books

Question 33

33 Display all table names in the current database using SQL

Answer 33

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'

Question 34

34 Insert the current timestamp into created_at in the logs table

Answer 34

INSERT INTO logs (created_at) VALUES (CURRENT_TIMESTAMP)

Question 35

35 Display the total number of rows in the customers table

Answer 35

SELECT COUNT(*) FROM customers