Database Management System Laboratory for BCA 4th

Imagine a library, but instead of books, it stores information. This information can be anything from people’s names and addresses to product details or even medical records. A Database Management System (DBMS) is like the librarian in this information library. It keeps everything organized, helps you find what you need quickly, and makes sure nobody messes things up.

Here’s what a DBMS does:

Meaning:

• Database: A collection of organized information.
• Management System: A software program that helps you control and use the information.

Use:

• Store information: Save data in an organized way.
• Find information: Search for specific details quickly and easily.
• Update information: Edit or change existing data.
• Delete information: Remove data that’s no longer needed.
• Protect information: Keep data safe from unauthorized access.

Where:

Any organization: Businesses, hospitals, schools, libraries, even your phone!

Example:

Think of an online shopping website. When you search for a product, the website uses a DBMS to find it in its “information library.” When you buy something, the DBMS updates the product’s quantity and adds your order details. It also keeps your profile information safe and secure.

So, a DBMS is like a super-organized librarian that helps us store, find, and manage information easily and securely. It’s used everywhere, from websites to your phone, making our lives a lot more organized!

Let’s Begin Practical!

Assignment 1: Use of CREATE, ALTER, RENAME, and DROP statements in the database tables (relations)

1. CREATE: Purpose: To create a new table in the database.

Example:

CREATE TABLE customers (

  customer_id INT PRIMARY KEY,

  name VARCHAR(50),

  email VARCHAR(100)

);

Output: A new table named customers with the specified columns is created.

2. ALTER: Purpose: To modify the structure of an existing table.

Example:

ALTER TABLE customers

ADD phone_number VARCHAR(20);

Output: A new column named phone_number is added to the customer’s table.

3. RENAME: Purpose: To change the name of an existing table.

Example:

RENAME TABLE customers TO clients;

Output: The name of the table customers is changed to clients.

4. DROP: Purpose: To delete a table from the database.

Example:

DROP TABLE clients;

Output: The table clients are permanently deleted from the database.

Important Notes:

• These statements are part of the SQL (Structured Query Language) used for interacting with databases.
• The exact syntax and output may vary slightly depending on the specific DBMS you’re using (e.g., MySQL, PostgreSQL, Oracle).
• It’s essential to use these statements carefully, as they can make irreversible changes to your database.
• Always back up your database before making significant modifications.

Assignment 2: Use of INSERT INTO, DELETE, and UPDATE statements in the database tables (relations)

1. INSERT INTO: Purpose: Adds new rows (records) to a table.

Example:

INSERT INTO customers (name, email)

VALUES (‘Alice Smith’, ‘alice@example.com’);

Output: A new row is added to the customer’s table with the specified values for name and email.

2. DELETE: Purpose: Removes rows from a table.

Example:

DELETE FROM customers

WHERE customer_id = 10;

Output: The row with customer_id = 10 is deleted from the customer’s table.

3. UPDATE: Purpose: Modifies existing values in rows of a table.

Example:

UPDATE customers

SET email = ‘bob.johnson@example.com’

WHERE customer_id = 5;

Output: The email value for the row with customer_id = 5 is changed to bob.johnson@example.com.

Key Points:

• These statements are also part of SQL.
• The WHERE clause is often used with DELETE and UPDATE to specify which rows should be affected.
• It’s crucial to use these statements cautiously, as they can permanently alter your database data.
• Consider making backups before executing significant changes.

Assignment 3: Use of simple select statement.

Purpose: The SELECT statement is like a flashlight that lets you view specific information stored in database tables. It’s the primary tool for retrieving data from a database.

Basic Syntax:

SELECT columns

FROM table_name;

Breaking It Down:

SELECT: This keyword tells the database you want to retrieve data.

columns: Here, you specify the names of the columns (fields) you want to see.

FROM: This keyword indicates the table you’re retrieving data from.

Example 1: Selecting All Columns:

SELECT *

FROM customers;

This query fetches all columns and rows from the customer’s table. It’s like saying, “Show me everything in that table!”

Example 2: Selecting Specific Columns:

SELECT name, email

FROM customers;

This query only retrieves the name and email columns, showing you those specific details for each customer.

Assignment 4: Use of select query on two relations

Here’s a simple explanation of how to use SELECT queries on two relations (tables) with an example: Imagine: Two tables:

• Table 1: customers (stores customer information)
• Table 2: orders (stores order details for each customer)
• Want to: Find the names of customers who placed orders and what they ordered.

How to: Use a SELECT query that combines information from both tables.

Key Concept: Joins

• Joins: Connect tables based on shared columns (like a bridge).
• Common Join Types:
  • INNER JOIN: Combines rows where matching values exist in both tables.
  • LEFT JOIN (or LEFT OUTER JOIN): Includes all rows from the left table, even with no matches in the right table.

Example (INNER JOIN):

SELECT customers.name, orders.product_name

FROM customers

INNER JOIN orders

ON customers.customer_id = orders.customer_id;

Explanation:

• SELECT: Choose columns to display (customer name and product name).
• FROM customers: Start with the customer’s table.
• INNER JOIN orders: Connect to the orders table using a bridge.
• ON customers.customer_id = orders.customer_id: Match rows based on these shared IDs.

Result: A combined list showing customer names and their corresponding product orders.

Remember:

• Use appropriate join types based on your needs.
• Specify the correct columns for joining.
• Refine results further using WHERE, ORDER BY, and other clauses.

Assignment 5: Use of nesting of queries.

Imagine: Abookcase with drawers, and within those drawers, there are smaller boxes. Nesting queries is like opening a drawer and then reaching into a specific box within it to find something special.

Purpose: It’s like asking a question within a question, allowing you to refine your search and get more precise results from the database.

How It Works:

• Outer Query: The main query, like opening the drawer.
• Inner Query: A query within the outer query, like opening the box inside the drawer.
• Output: The inner query’s result is used by the outer query to make selections or comparisons.

Example:

SELECT name

FROM customers

WHERE customer_id IN (

    SELECT customer_id

    FROM orders

    WHERE product_id = 5

);

Explanation:

• Outer Query: Selects names from the customer’s table.
• Inner Query: Finds customer IDs who ordered product 5.
• Link: The outer query uses the IN operator to include only customers from the inner query’s result.

Result: The names of customers who ordered product 5.

Key Points:

• Nesting queries can be used with various SQL commands like SELECT, WHERE, and HAVING.
• It’s helpful for complex filtering and data retrieval.
• Use parentheses to enclose inner queries for clarity.
• Be cautious of performance implications with overly complex nested queries.

Assignment 6: Use of aggregate functions.

Imagine a team of super-efficient librarians who can quickly summarize huge collections of books. Aggregate functions in SQL are like those librarians but for a database!

What They Do:

• Calculate summaries: They take multiple values from a column and produce a single value that represents a summary of the whole group.
• Help you make sense of large datasets: They provide quick insights and reveal patterns that might not be obvious when looking at individual rows.

Common Aggregate Functions:

• COUNT: Counts the number of items in a column (like counting books on a shelf).
• SUM: Adds up all the values in a column (like calculating the total weight of all the books).
• AVG: Finds the average value in a column (like determining the average length of the books).
• MIN: Retrieves the smallest value in a column (like finding the thinnest book).
• MAX: Retrieves the largest value in a column (like finding the thickest book).

How to Use Them:

• Combine with SELECT: Use them within a SELECT statement to apply them to specific columns.
• Often used with GROUP BY: Group data based on certain criteria before applying aggregate functions.

Example:

SELECT COUNT(*) AS total_orders,

       SUM(price) AS total_sales

FROM orders;

Use code with caution. Learn more

Explanation:

• This query counts the total number of orders and calculates the total sales amount from the orders table.
• COUNT(*) counts all rows, even with NULL values.
• SUM(price) adds up all the values in the price column.

Key Points:

• Aggregate functions are essential for data analysis and reporting.
• They can be used with various numeric and date/time columns.
• Use them to gain valuable insights from your database.

Assignment 7: Use of substring comparison.

Imagine a detective searching for clues within a secret message. Substring comparison is like magnifying glass, letting you zoom in on specific parts of text to uncover hidden patterns or matches!

What It Does:

• Isolates portions of text: It allows you to focus on specific segments within a larger string of characters, just like examining a single word within a sentence.
• Comparisons: It enables you to compare these substrings with other strings or patterns to check for similarities or differences.

How It Works:

• Identify the target string: The text you want to analyze.
• Specify the substring: The segment you want to isolate and compare.
• Use comparison operators: Check if the substring matches, contains, or starts/ends with certain patterns.

Common Operators:

• LIKE: Checks for patterns within strings (e.g., name LIKE ‘%Smith%’ finds names containing “Smith”).
• SUBSTRING() (or similar functions): Extracts a substring based on starting and ending positions (e.g., SUBSTRING(email, 1, 5) extracts the first 5 characters of an email address).

Example (SQL):

SELECT *

FROM customers

WHERE email LIKE ‘%@gmail.com’;

Explanation:

• This query finds customers whose email addresses end with “@gmail.com”.
• The % wildcard represents any characters before “@gmail.com”.

Key Points:

• Substring comparison is crucial for pattern matching and filtering text data.
• It’s used in various programming languages and database systems.
• It’s a powerful tool for extracting meaningful information from text-based data.

Assignment 8: Use of order by statement.

Imagine a librarian organizing books on shelves. The ORDER BY statement is your trusty assistant, helping you arrange data in a specific order, making it easier to find what you need!

What It Does:

• Sorts results: It arranges the rows returned by a query based on one or more columns, just like alphabetizing books by title or author.
• Improves readability: It makes data easier to understand and analyze by presenting it in a logical order.

How It Works:

• Place it at the end of a SELECT query: It comes after the WHERE clause (if used).
• Specify columns: Indicate which columns to use for sorting, separated by commas.
• Default order: Ascending (A-Z, 1-10).
• Descending order: Add the keyword DESC after the column name.

Example:

SELECT name, age

FROM customers

ORDER BY age;   — Ascending order by age

Explanation:

• This query sorts customers by age in ascending order, listing the youngest first.

Multiple Columns:

SELECT name, city

FROM customers

ORDER BY city, name;   — Sort by city, then name within each city

Descending Order:

SELECT product_name, price

FROM products

ORDER BY price DESC;   — Sort by price in descending order (highest first)

Key Points:

• ORDER BY is essential for organizing results and enhancing readability.
• It can be used with various data types (text, numbers, dates).
• It’s a valuable tool for presenting data in a meaningful way.

Assignment 9: Consider the following schema for a Library Database:

BOOK (Book_id, Title, Publisher_Name, Pub_Year)

BOOK_AUTHORS (Book_id, Author_Name)

PUBLISHER (Name, Address, Phone)

BOOK_COPIES (Book_id, Branch_id, No-of_Copies)

BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date)

LIBRARY_BRANCH (Branch_id, Branch_Name, Address)

Write SQL queries to

1. Retrieve details of all books in the library_id, title, name of publisher, authors, number of copies in each branch, etc.

2. Get the particulars of borrowers who have borrowed more than 3 books between Jan 2018 to Jun 2018

3. Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.

4. Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.

5. Create a view of all books and its number of copies that are currently available in the Library. 

The SQL queries for the given tasks:

1. Retrieve details of all books:

SELECT b.Book_id, b.Title, p.Name AS Publisher_Name, 

       GROUP_CONCAT(ba.Author_Name SEPARATOR ‘, ‘) AS Authors, 

       bc.Branch_id, bc.No_of_Copies

FROM BOOK b

JOIN PUBLISHER p ON b.Publisher_Name = p.Name

JOIN BOOK_AUTHORS ba ON b.Book_id = ba.Book_id

JOIN BOOK_COPIES bc ON b.Book_id = bc.Book_id

GROUP BY b.Book_id, b.Title, p.Name, bc.Branch_id, bc.No_of_Copies;

2. Get particulars of borrowers with more than 3 books between Jan 2018 to Jun 2018:

SELECT bl.Card_No, COUNT(*) AS Borrowed_Books

FROM BOOK_LENDING bl

WHERE Date_Out BETWEEN ‘2018-01-01’ AND ‘2018-06-30’

GROUP BY bl.Card_No

HAVING COUNT(*) > 3;

3. Delete a book and update related tables:

START TRANSACTION;

DELETE FROM BOOK WHERE Book_id = 1234;  — Replace 1234 with the actual Book_id

DELETE FROM BOOK_AUTHORS WHERE Book_id = 1234;

DELETE FROM BOOK_COPIES WHERE Book_id = 1234;

DELETE FROM BOOK_LENDING WHERE Book_id = 1234;

COMMIT;

4. Partition BOOK table based on year of publication:

ALTER TABLE BOOK PARTITION BY RANGE (Pub_Year) (

    PARTITION p2000 VALUES LESS THAN (2001),

    PARTITION p2001 VALUES LESS THAN (2002),

    PARTITION p2002 VALUES LESS THAN (2003),

    — … add more partitions as needed

    PARTITION p2023 VALUES LESS THAN MAXVALUE

);

— Example query using partitioning:

SELECT * FROM BOOK PARTITION (p2015);  — Retrieve books published in 2015

5. Create a view of available books:

CREATE VIEW AvailableBooks AS

SELECT b.Book_id, b.Title, bc.Branch_id, bc.No_of_Copies

FROM BOOK b

JOIN BOOK_COPIES bc ON b.Book_id = bc.Book_id

WHERE bc.No_of_Copies > 0;

Assignment 10: Consider the following schema for Order Database:

SALESMAN (Salesman_id, Name, City, Commission)

CUSTOMER (Customer_id, Cust_Name, City, Grade, Salesman_id)

ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)

Write SQL queries to

1. Count the customers with grades above Amritsar’s average.

2. Find the name and numbers of all salesmen who had more than one customer.

3. List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION operation.)

4. Create a view that finds the salesman who has the customer with the highest order of a day.

5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.

Here are the SQL queries for the given tasks, along with explanations:

1. Count customers with grades above Amritsar’s average:

SELECT COUNT(*) AS Count_Above_Average

FROM CUSTOMER

WHERE Grade > (SELECT AVG(Grade) FROM CUSTOMER WHERE City = ‘Amritsar’);

Explanation:

• Calculates the average grade in Amritsar using a subquery.
• Filters customers with grades higher than that average.
• Counts those customers using COUNT(*).

2. Find names and numbers of salesmen with more than one customer:

SELECT Salesman_id, Name

FROM SALESMAN

WHERE Salesman_id IN (SELECT Salesman_id FROM CUSTOMER GROUP

 BY Salesman_id HAVING

 COUNT(*) >

 1);

Explanation:

• Identifies salesmen with more than one customer using a subquery.
• Retrieves their names and IDs from the SALESMAN table.

3. List salesmen with and without customers in their cities:

SELECT Salesman_id, Name, City, ‘Has Customers’ AS Status

FROM SALESMAN

WHERE Salesman_id IN (SELECT Salesman_id FROM CUSTOMER)

UNION

SELECT Salesman_id, Name, City, ‘No Customers’ AS Status

FROM SALESMAN

WHERE Salesman_id NOT IN (SELECT Salesman_id FROM CUSTOMER);

Explanation:

• Uses UNION to combine results from two queries.
• First query finds salesmen with customers in their cities.
• Second query finds those without customers in their cities.

4. Create a view for salesman with the highest order of a day:

CREATE VIEW HighestOrderSalesman AS

SELECT s.Salesman_id, s.Name, o.Ord_Date, o.Purchase_Amt

FROM SALESMAN s

JOIN ORDERS o ON s.Salesman_id = o.Salesman_id

WHERE o.Purchase_Amt = (

    SELECT MAX(Purchase_Amt)

    FROM ORDERS

    WHERE Ord_Date = o.Ord_Date

);

Explanation:

• Joins SALESMAN and ORDERS tables.
• Finds the highest order amount for each day using a subquery.
• Filters orders match those highest amounts.

5. Delete salesman with ID 1000 and their orders:

START TRANSACTION;

DELETE FROM ORDERS WHERE Salesman_id = 1000;

DELETE FROM SALESMAN WHERE Salesman_id = 1000;

COMMIT;

Explanation:

• Uses a transaction to ensure data integrity.
• Deletes orders associated with the salesman first.
• Then deletes the salesman record itself.

Assignment 11: Write a PL/SQL code to add two numbers and display the result. Read the numbers during run time.

Code: 

DECLARE

  num1 NUMBER;

  num2 NUMBER;

  sum NUMBER;

BEGIN

  — Prompt the user to enter the first number

  DBMS_OUTPUT.PUT_LINE(‘Enter the first number: ‘);

  — Read the input from the user

  DBMS_INPUT.GET_LINE(num1);

  — Prompt the user to enter the second number

  DBMS_OUTPUT.PUT_LINE(‘Enter the second number: ‘);

  — Read the input from the user

  DBMS_INPUT.GET_LINE(num2);

  — Add the two numbers and store the result in the sum variable

  sum := num1 + num2;

  — Display the result

  DBMS_OUTPUT.PUT_LINE(‘The sum of ‘ || num1 || ‘ and ‘ || num2 || ‘ is ‘ || sum);

END;

/

Explanation:

• DECLARE: Declares variables to store the numbers and the sum.
• BEGIN: Marks the start of the executable code block.
• DBMS_OUTPUT.PUT_LINE: Displays prompts for entering numbers.
• DBMS_INPUT.GET_LINE: Reads input from the user and stores it in the variables num1 and num2.
• sum := num1 + num2: Adds the two numbers and assigns the result to sum.
• DBMS_OUTPUT.PUT_LINE: Displays the calculated sum.

END; Marks the end of the code block.

/: Signals the end of the PL/SQL block to the database.

Output:

Enter the first number: 25

Enter the second number: 30

The sum of 25 and 30 is 55

Assignment 12: Write a PL/SQL code to find sum of first 10 natural numbers using while and for loop.

Using a while loop:

Code

DECLARE

  num NUMBER := 1;  — Start with the first natural number

  sum NUMBER := 0;  — Initialize the sum to 0

BEGIN

  WHILE num <= 10 LOOP

    sum := sum + num;  — Add the current number to the sum

    num := num + 1;  — Increment the number for the next iteration

  END LOOP;

  DBMS_OUTPUT.PUT_LINE(‘The sum of first 10 natural numbers using while loop is: ‘ || sum);

END;

/

Explanation:

• Declares variables num and sum.
• Initializes num to 1 and sum to 0.
• Iterates using the WHILE loop as long as num is less than or equal to 10.
• In each iteration, adds num to sum and increments num.
• Displays the final sum.

Using a for loop:

Code

DECLARE

  sum NUMBER := 0;  — Initialize the sum to 0

BEGIN

  FOR i IN 1..10 LOOP  — Loop from 1 to 10

    sum := sum + i;  — Add the current number to the sum

  END LOOP;

  DBMS_OUTPUT.PUT_LINE(‘The sum of first 10 natural numbers using for loop is: ‘ || sum);

END;

/

Explanation:

• Declares a variable sum and initializes it to 0.
• Uses a FOR loop that automatically iterates from 1 to 10.
• In each iteration, adds the loop counter i to sum.
• Displays the final sum.

Output (for both loops):

The sum of first 10 natural numbers using while loop is: 55

The sum of first 10 natural numbers using for loop is: 55

Assignment 13: Write a program to create a trigger which will convert the name of a student to upper case before inserting or updating the name column of student table.

Code

CREATE OR REPLACE TRIGGER student_name_uppercase

BEFORE INSERT OR UPDATE ON student

FOR EACH ROW

BEGIN

  IF :NEW.name IS NOT NULL THEN  — Check if the name is not null

    :NEW.name := UPPER(:NEW.name);  — Convert the name to uppercase

  END IF;

END;

/

• Explanation:
• CREATE OR REPLACE TRIGGER: Creates or replaces the trigger named student_name_uppercase.
• BEFORE INSERT OR UPDATE ON student: Activates the trigger before any insert or update operation on the student table.
• FOR EACH ROW: Executes the trigger code for each affected row.
• IF :NEW.name IS NOT NULL THEN: Checks if the new value being inserted or updated for the name column is not null.
• :NEW.name := UPPER(:NEW.name);: Converts the name to uppercase using the UPPER function and assigns it back to the name column.

Output:

• Insert: If you try to insert a student with the name “john doe”, the trigger will automatically convert it to “JOHN DOE” before insertion.
• Update: If you update an existing student’s name to “mary smith”, the trigger will convert it to “MARY SMITH” before updating the record.

Key Points:

• The trigger ensures data consistency by always storing student names in uppercase.
• It avoids the need for manual uppercase conversion during data entry.
• It’s a good example of using triggers for data validation and standardization.

Assignment 14: Write a PL/SQL block to count the number of rows affected by an update statement using SQL%ROWCOUNT

Code 

DECLARE

  rows_updated NUMBER;

BEGIN

  UPDATE student SET city = ‘New City’ WHERE age > 25;  — Update statement

  rows_updated := SQL%ROWCOUNT;  — Capture the number of rows affected

  DBMS_OUTPUT.PUT_LINE(‘Number of rows updated: ‘ || rows_updated);

END;

/

Explanation:

• DECLARE: Declares a variable rows_updated to store the count.
• UPDATE student …: Performs the update operation.
• rows_updated := SQL%ROWCOUNT;: Assigns the number of affected rows to rows_updated.
• DBMS_OUTPUT.PUT_LINE(…): Displays the count of updated rows.

Output:

Number of rows updated: 12  — 12 rows matched the condition and were updated

Key Points:

• SQL%ROWCOUNT is a built-in attribute that automatically holds the count of rows affected by the most recent DML statement (INSERT, UPDATE, DELETE, or MERGE).
• It’s useful for tracking changes and verifying the impact of data manipulation operations.
• It’s a valuable tool for debugging and ensuring data integrity in PL/SQL programs.

Assignment 15: Write a PL/SQL block to increase the salary of all doctors by 1000.

Code 

DECLARE

  rows_updated NUMBER;

BEGIN

  UPDATE employees  — Assuming the table name is “employees”

  SET salary = salary + 1000  — Increase salary by 1000

  WHERE job_title = ‘Doctor’;  — Target only doctors

  rows_updated := SQL%ROWCOUNT;  — Capture the number of rows affected

  DBMS_OUTPUT.PUT_LINE(‘Number of doctors whose salaries were increased: ‘ || rows_updated);

END;

/

Explanation:

• DECLARE: Declares a variable rows_updated to store the count of affected rows.
• UPDATE employees …: Performs the salary increase operation on the employees table (adjust the table name if different).
• SET salary = salary + 1000: Increments the salary column by 1000.
• WHERE job_title = ‘Doctor’: Ensures the update applies only to doctors.
• rows_updated := SQL%ROWCOUNT;: Stores the count of updated rows.
• DBMS_OUTPUT.PUT_LINE(…): Displays the number of doctors whose salaries were changed.

Output (5 doctors were updated):

Number of doctors whose salaries were increased: 5