1. Create a table called students where each student has an id, name, and age. Ensure that the id uniquely identifies each student.
So this qn asks us to create a table in sql by the name student and give attributes id, name and age. Since id has to be unique we can make it as a primary key so that each value in id is unique and not null.
2. Create a table employees where name and email cannot be empty, but phone_number can be optional.
Similarly we create a table named employees and give attributes name, email and phone_number. Since name and email cannot be empty we give not null to those attributes.
3. Create a table users where both username and email must be unique across all records.
After creating table users, for the attributes username and email we apply "unique" constraint so that all values are unique.
4. Create a table products where price must always be greater than 0 and stock cannot be negative.
For this we give constraints in price to check price>0 and then we check stock >=0.
5. Create a table orders where status should default to 'pending' if no value is provided, and created_at should store the current timestamp automatically.
For this we create a table orders with attributes status and created_at. Since status should automatically be 'pending' if no value is given, we use the default constraint. Similarly, to store the current time automatically, we use default current_timestamp for created_at.
6. Create a table accounts where
account_number must be unique and not null
balance must always be greater than or equal to 0
Here we create a table accounts with attributes account_number and balance. Since account_number should not be empty and must be unique, we apply both not null and unique constraints. For balance, we ensure it never goes below 0 by using a check constraint (balance >= 0).
7. Create a table enrollments where a student can enroll in multiple courses, but the combination of student_id and course_id must be unique.
In this case we create a table enrollments with student_id and course_id. A student can take multiple courses, but the same student should not enroll in the same course more than once. So we apply a composite unique constraint.
8.
Create two tables:
departments with id and name
employees with id, name, and department_id
Ensure that department_id in employees must exist in departments.
First we create a departments table with id and name. Then we create an employees table with id, name, and department_id. To make sure every department_id in employees actually exists in departments, we use a foreign key constraint referencing departments(id).
9.
Modify the previous foreign key example so that:
When a department is deleted, all related employees are also deleted
When a department ID is updated, it reflects in the employees table
Here we extend the foreign key constraint. Along with referencing departments(id), we add on delete cascade so that deleting a department automatically deletes related employees. We also add on update cascade so that any change in department id is reflected in the employees table.
Top comments (0)