DBMS Experiment 3

 Question:


Solution:

Database Management System (e.g., MySQL, PostgreSQL, Oracle)

Client tools (e.g., MySQL Workbench, pgAdmin)

Optional: Backup and monitoring tools


Question:


Answer:

VIII. Precautions

1) Use of appropriate syntax
Always write SQL commands following the correct syntax rules specific to the DBMS to avoid errors.

2) Select required relations and use relevant conditions
Choose only necessary tables and apply precise WHERE clauses to retrieve accurate and optimized query results.

IX. Additional Resources used

  • Official DBMS documentation (e.g., MySQL, PostgreSQL manuals)

  • SQL tutorials and guides (e.g., W3Schools, TutorialsPoint)

  • DBMS client tools (e.g., MySQL Workbench, pgAdmin)

  • Online forums and communities (e.g., Stack Overflow)

  • Backup utilities and monitoring software

X. Result (Output of the procedure)
The output includes successful database creation, table structures with assigned keys, updated tables as required, and correctly executed queries returning expected data or confirmation messages.



Question:





Solution:

1. Create a table EMPLOYEE with the following schema:

Emp (EMP_no as primary key, E_name, Dept_no, Dept_name, Job_id, salary)

Answer:

CREATE TABLE EMPLOYEE (
  EMP_no INT PRIMARY KEY,
  E_name VARCHAR(50),
  Dept_no INT,
  Dept_name VARCHAR(50),
  Job_id VARCHAR(20),
  salary DECIMAL(10,2)
);

2. Create tables EMPLOYEE and DEPARTMENT with primary and foreign key constraints:

Emp(empno as primary key, empname, salary, phoneno)
Dept(deptno primary key, empno foreign key, deptname, location)

Answer:

CREATE TABLE EMPLOYEE (
  empno INT PRIMARY KEY,
  empname VARCHAR(50),
  salary DECIMAL(10,2),
  phoneno VARCHAR(15)
);

CREATE TABLE DEPARTMENT (
  deptno INT PRIMARY KEY,
  empno INT,
  deptname VARCHAR(50),
  location VARCHAR(50),
  FOREIGN KEY (empno) REFERENCES EMPLOYEE(empno)
);

3. List DDL commands with their syntax.

Answer:

  • CREATE: CREATE TABLE table_name (...);

  • ALTER: ALTER TABLE table_name ADD column_name datatype;

  • DROP: DROP TABLE table_name;

  • TRUNCATE: TRUNCATE TABLE table_name;

  • RENAME: RENAME TABLE old_name TO new_name;

4. List different SQL binary data types.

Answer:

  • BINARY

  • VARBINARY

  • BLOB (Binary Large Object)

  • LONGBLOB, MEDIUMBLOB, TINYBLOB (MySQL-specific)

5. Write the difference between DROP and TRUNCATE commands.

Answer:

  • DROP: Deletes the table structure and data permanently.

  • TRUNCATE: Deletes only data, keeps table structure intact.

6. Write the use of the DESCRIBE command.

Answer:

  • DESCRIBE table_name; is used to display the structure of a table—column names, data types, and constraints.


Sample Data:


-- Insert into EMPLOYEE (with empno 1001, 1002 etc.)

INSERT INTO EMPLOYEE (empno, empname, salary, phoneno) VALUES

(101, 'Ravi Kumar', 55000.00, '9876543210'),

(102, 'Priya Sharma', 62000.00, '9123456780'),

(103, 'Amit Patel', 48000.00, '9988776655'),

(104, 'Sunita Joshi', 70000.00, '9012345678'),

(105, 'Rajesh Verma', 53000.00, '9876501234');



INSERT INTO DEPARTMENT (deptno, empno, deptname, location) VALUES

(201, 101, 'Finance', 'Mumbai'),

(202, 102, 'Human Resources', 'Delhi'),

(203, 103, 'IT', 'Bengaluru'),

(204, 104, 'Marketing', 'Hyderabad'),

(205, 105, 'Operations', 'Chennai');






✅ Compatible Parts

  • The basic syntax for CREATE TABLE, INSERT INTO, PRIMARY KEY, and FOREIGN KEY is supported by SQLite.

  • VARCHAR(n) is allowed (SQLite treats it as TEXT internally).

  • DECIMAL(p,s) works but behaves like REAL due to SQLite’s dynamic typing.

🔧 Required Modifications for SQLite

1. DECIMAL ➝ REAL or NUMERIC

SQLite doesn’t enforce fixed precision. Use REAL or NUMERIC for salary.

Example change:

salary REAL

2. FOREIGN KEY Constraints

SQLite supports foreign keys, but you must enable them explicitly:

PRAGMA foreign_keys = ON;

3. DESCRIBE command

SQLite does not support DESCRIBE. Use:

PRAGMA table_info(EMPLOYEE);

✅ Updated SQLite-Compatible Version

Create Tables:

CREATE TABLE EMPLOYEE (
  empno INTEGER PRIMARY KEY,
  empname TEXT,
  salary REAL,
  phoneno TEXT
);

CREATE TABLE DEPARTMENT (
  deptno INTEGER PRIMARY KEY,
  empno INTEGER,
  deptname TEXT,
  location TEXT,
  FOREIGN KEY (empno) REFERENCES EMPLOYEE(empno)
);


INSERT INTO EMPLOYEE (empno, empname, salary, phoneno) VALUES
(101, 'Amit Sharma', 45000.00, '9876543210'),
(102, 'Neha Verma', 52000.00, '9988776655'),
(103, 'Rajesh Kumar', 60000.00, '9123456789'),
(104, 'Priya Singh', 48000.00, '9345678901'),
(105, 'Suresh Reddy', 55000.00, '9871234567');


INSERT INTO DEPARTMENT (deptno, empno, deptname, location) VALUES
(201, 101, 'Human Resources', 'Bangalore'),
(202, 102, 'Finance', 'Mumbai'),
(203, 103, 'IT', 'Hyderabad'),
(204, 104, 'Marketing', 'Delhi'),
(205, 105, 'Operations', 'Chennai');



Question:



Solution:

1. Create a table named stud with attributes Rollno, Studname, and Percentage. Apply a primary key on Rollno and ensure Percentage does not exceed 100.
Answer:

CREATE TABLE stud (
  Rollno INT PRIMARY KEY,
  Studname VARCHAR(50),
  Percentage DECIMAL(5,2) CHECK (Percentage <= 100)
);

2. Modify the stud table to add a new column named City.
Answer:

ALTER TABLE stud ADD City VARCHAR(50);

3. Increase the size of the Studname column by 10 characters.
Answer:

ALTER TABLE stud MODIFY Studname VARCHAR(60);



Insert sample rows into the stud table
Answer:

INSERT INTO stud (Rollno, Studname, Percentage, City) VALUES
(1, 'Aarav Sharma', 92.5, 'Delhi'),
(2, 'Priya Patel', 85.3, 'Ahmedabad'),
(3, 'Rahul Verma', 78.0, 'Lucknow'),
(4, 'Sneha Iyer', 88.7, 'Chennai'),
(5, 'Karan Mehta', 95.0, 'Mumbai');


Popular posts from this blog