Scribd
Upload
64 views9 pages

dbms ass

Uploaded by

chess.001x
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
64 views9 pages

dbms ass

Uploaded by

chess.001x
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 9

Laboratory Assignments List

Practical: Lab Course based on DSC-301: RDBMS with MySQL

1. Write a SQL statement to create a table with appropriate fields and apply following
integrity constraint on appropriate fields

a. Primary Key

b. Foreign key

c. Unique key

d. Null

e. Check

f. Default

ans:-

- In this SQL statement:

• id field is set as the primary key.


• name field is set as NOT NULL.
• email field is set as UNIQUE.
• department_id field is set as a foreign key referencing the id field of the Departments
table.
• is_active field is set with a default value of true.
• age field is checked with a CHECK constraint to ensure it's within a valid range (e.g.,
between 18 and 100).
2. Write a SQL statement to alter above table to add a new column and add new constraint
to that column.

Ans;-

To alter the 'Employee' table to add a new column and apply a new constraint to that column,
you can use the following SQL statement:

This SQL statement adds a new column 'hire_date' of type 'DATE' to the 'Employee' table and
applies a constraint 'chk_hire_date' to ensure that the 'hire_date' is not in the future.
3. Write a SQL statement to drop a table.

Ans;-

To drop a table in SQL, we can use the 'DROP TABLE' statement followed by the table name.
Here's the SQL statement to drop a table named 'Employee':

This statement will permanently delete the 'Employee' table and all its associated data. it
cannot be undone.
4. Write a SQL statement to Create a table and do the following using different SQL
commands.

a. Insert 10 appropriate records

b. Update a record

c. Delete a record

ans;-

example SQL script that creates a table named 'Students', inserts 10 records, updates one of
the records, and then deletes a record:

In this SQL script:

• We create a table named 'Students' with fields for 'id', 'name', 'age', and 'grade'.
• We insert 10 records into the 'Students' table.
In this SQL script:

• We update the age of the student with 'id' 3 to 24.

In this SQL script:

• We delete the record of the student with 'id' 7.


5. Write a SQL select statement to perform different SQL Operators.

Ans;-

example SQL SELECT statement that demonstrates the use of different SQL operators:

These examples demonstrate various SQL operators such as SELECT, DISTINCT, WHERE,
ORDER BY, JOIN, GROUP BY, AND, OR, LIKE, IN, and BETWEEN.
6. Write a SQL select statement with different SQL clauses.

Ans;-

example SQL SELECT statement that incorporates various SQL clauses:

In this SQL statement:

• 'SELECT' clause specifies the columns to be retrieved from the 'Employees' table.
• 'FROM' clause specifies the source table ('Employees').
• 'WHERE' clause filters the rows where the 'department_id' is equal to 100.
• 'ORDER BY' clause sorts the result set based on the 'hire_date' column in descending
order.
• 'LIMIT' clause restricts the number of rows returned to 10.

7. Write a SQL select statement with different SQL functions.

Ans;-

example SQL SELECT statement that utilizes various SQL functions:


In this SQL statement:

• 'CONCAT()' function is used to concatenate the 'first_name' and 'last_name' columns to


create a full name.
• 'UPPER()' function converts the 'job_title' column to uppercase.
• 'DATE_FORMAT()' function formats the 'hire_date' column to display in 'YYYY-MM-DD'
format.
• 'ROUND()' function calculates the annual salary by multiplying the 'salary' column by 12
and rounds it to 2 decimal places.
• 'COUNT()' function counts the total number of employees.
• 'MAX()', 'MIN()', and 'AVG()' functions calculate the maximum, minimum, and average
salary respectively.
• 'GROUP BY' clause groups the result set by 'department_id'.
• 'HAVING' clause filters the result set based on the average salary being greater than
50000.
• 'ORDER BY' clause sorts the result set by the average salary in descending order.
8. Write a SQL select statement using subqueries and joins.

Ans;-

example SQL SELECT statement that uses subqueries and joins:

In this SQL statement:

• The main query selects 'employee_id', 'first_name', 'last_name', and 'department_name'


from the 'Employees' table ('e') joined with the 'Departments' table ('d') on
'department_id'.
• The subquery '(SELECT AVG(salary) FROM Employees)' calculates the average salary of
all employees.
• The 'WHERE' clause filters the result set to include only employees whose salary is
greater than the average salary calculated by the subquery.
• The result set is ordered by salary in descending order using the 'ORDER BY' clause.

You might also like