Scribd
Upload
5 views4 pages

Exercise-1 Lab Topic

The document outlines exercises for the Department of Computer Science and Engineering at Raghu Engineering College, focusing on SQL tasks such as creating, altering, and dropping tables, views, and constraints. It includes specific instructions for creating tables for students, courses, employees, and departments, along with various alterations and constraints to be applied. Additionally, it covers creating and dropping views and creating new tables based on existing data.

Uploaded by

roronoazoroooo770
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views4 pages

Exercise-1 Lab Topic

The document outlines exercises for the Department of Computer Science and Engineering at Raghu Engineering College, focusing on SQL tasks such as creating, altering, and dropping tables, views, and constraints. It includes specific instructions for creating tables for students, courses, employees, and departments, along with various alterations and constraints to be applied. Additionally, it covers creating and dropping views and creating new tables based on existing data.

Uploaded by

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

RAGHU ENGINEERING COLLEGE

Autonomous
(Approved by AICTE, New Delhi, Accredited by NBA (CIV, ECE, MECH, CSE), NAAC with ‘A+’ grade
& Permanently Affiliated to JNTU-GV Vizianagaram)
Dakamarri, Bheemunipatnam Mandal, Visakhapatnam Dist. – 531 162 (A.P.)
Ph: +91-8922-248001, 248002 Fax: + 91-8922-248011
e-mail: principal@[Link] website: [Link]

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

Exercise – 1: Queries for Creating, Dropping, and Altering Tables, Views,


and Constraints

Note:
Write the syntax for each task
After completion of each task describe the table structure
1) Creating Tables
Create student,course, employee and dept tables with the following columns.
a) Student(sid:number, name: string, marks: string)
b) Courses(cid:number, cname:string, credit_points:number)
c) Employee(name:integer, salary:number);
d) Dept(did:string, name:string, location:string);
e) Location(id:string,city:string,state:string)

2) Altering database objects(TABLES)


a) Adding a new column
i) Add a column email-id:string, state:string to student table
ii) Add columns deptid: number, address:string to employee table
iii) Add a column budget: number to dept table
iv) Add a column pincode:number to location table
b) Changing the datatype width of a column (increase/decrease the size of the
column)
i) Change the name datatype size to 20 in student table
ii) Change the cname datatype size to 5 in courses table
iii) Change the salary datatype to accept 6 digit number
c) Changing the datatype for a column
i) Change the did type to number in dept table
ii) Change the marks column type to number in student table
RAGHU ENGINEERING COLLEGE
Autonomous
(Approved by AICTE, New Delhi, Accredited by NBA (CIV, ECE, MECH, CSE), NAAC with ‘A+’ grade
& Permanently Affiliated to JNTU-GV Vizianagaram)
Dakamarri, Bheemunipatnam Mandal, Visakhapatnam Dist. – 531 162 (A.P.)
Ph: +91-8922-248001, 248002 Fax: + 91-8922-248011
e-mail: principal@[Link] website: [Link]

iii) Change employee table name column datatype to string


d) Renaming a column
i) Rename column cid to course_id in course table
ii) Rename column credit_points to credits in course table
iii) Rename column marks to cgpa in student table
e) Adding a constraint
NOT NULL
i) Add NOT NULL constraint on name in course table
ii) Add column AGE number with NOT NULL constraint in student table
iii) Add a new column contact with NOT NULL constraint in employee table
iv) Add NOT NULL constraint on pincode in location table
DEFAULT
i) Set credit default to 3 in course table
ii) Set location default to ‘VSKP’ in dept table
UNIQUE
i) Add unique constraint on email-id in student table
ii) Add a new column contact with NOT NULL UNIQUE constraints in
employee table
CHECK
i) Add check constraint on salary to accept positive value in employee table
ii) Add check constraint on cgpa to accept value above 7.5 in student table
iii) Add check constraint on pincode to accept 6 digit number in location table
PRIMARY KEY
i) Add primary key constraint on course_id in course table
ii) Add primary key constraint on sid in student table
iii) Add column EID with primary key constraint in employee table
iv) Add primary key constraint on did in dept table

FOREIGN KEY
i) Add column CID along with foreign key constraints in student table
v) Add foreign key constraint on deptid in employee table
vi) Add column EID along with foreign key constraints in DEPT table
f) Renaming a table
i) Rename table student to student_Enrolled
ii) Rename table Employee to Employees_workin
RAGHU ENGINEERING COLLEGE
Autonomous
(Approved by AICTE, New Delhi, Accredited by NBA (CIV, ECE, MECH, CSE), NAAC with ‘A+’ grade
& Permanently Affiliated to JNTU-GV Vizianagaram)
Dakamarri, Bheemunipatnam Mandal, Visakhapatnam Dist. – 531 162 (A.P.)
Ph: +91-8922-248001, 248002 Fax: + 91-8922-248011
e-mail: principal@[Link] website: [Link]

g) Dropping constraints
i) Drop NOT NULL constraint on AGE column in student table
ii) Drop DEFAULT constraint on location column in dept table
iii) Drop UNIQUE constraint on email-id column in student table
iv) Drop CHECK constraint on cgpa COLUMN in student table
v) Drop foreign key constraint on EID column in DEPT table
h) Dropping a column
i) Drop column state from student table
ii) drop column eid from dept table
3) Dropping an existing table
i) Drop tables students_enrolled, employees_works, dept, courses

Create below table with the given cosntraints.

A product table ( "product ID primary key constraint", "product name not null",
"description", "price default 1", "category", "stock quantity >100")
Drop all constraint.
Drop products table.

Example: Defining more than constraint

create table example(id number constraint pp_id_nn_uq1_check not null unique check(id
between 10000 and 99999));
RAGHU ENGINEERING COLLEGE
Autonomous
(Approved by AICTE, New Delhi, Accredited by NBA (CIV, ECE, MECH, CSE), NAAC with ‘A+’ grade
& Permanently Affiliated to JNTU-GV Vizianagaram)
Dakamarri, Bheemunipatnam Mandal, Visakhapatnam Dist. – 531 162 (A.P.)
Ph: +91-8922-248001, 248002 Fax: + 91-8922-248011
e-mail: principal@[Link] website: [Link]

Note:
Write the syntax for each task
After completion of each task describe the table structure
and print the data in those tables.

4) Creating and Dropping Views


i) Create a view ‘MANAGER_DETAILS’ for the employee table that include all the
details of MANAGERS. List the information.
ii) Create a view ‘EMP_SALARIES’ for the employee table that includes employee
number, name, salary, job category with above 2000 salary. List the information
iii) Create a view ‘BEST_RATINGS’ for the sailors table that includes sailor name, age,
rating with the rating above 7. List the information.
iv) Drop views MANAGER_DETAILS, EMP_SALARIES and BEST_RATINGS

5) Creating tables based on existing tables


i) Create a new table with the data like employee name, dept number, job category and
their salaries from employee table.
ii) Create a new table with sailor name, age from sailors table whose rating is above 7.
iii) Create a new table ‘CLERKS_BACKUP’ with no data from employee table.

Common questions

Powered by AI

Changing a column's datatype from string to number involves risks of data corruption or loss if current data is not compatible with the numeric type. Existing string values must be convertible to numbers, or they need to be cleaned or removed. This alteration can enhance efficiency in data operations but must be executed with caution and thorough data validation .

Dropping a Primary Key constraint removes the unique identifier for each record in the table, which can lead to duplicate entries and loss of data integrity. It affects related tables' foreign key constraints, potentially causing orphan records. Table indexing efficiency might be reduced, leading to slower data retrieval operations .

Creating views like 'MANAGER_DETAILS' provides a tailored, simplified representation of data, enhancing accessibility and security by presenting only relevant information to users. It streamlines permissions and grants users access to complex queries without exposing underlying table structures. Strategic implementation promotes efficiency, reduces data access errors, and supports role-specific data management .

Dropping constraints like the CHECK on 'cgpa' can often be necessary when business rules change. However, it risks data validity issues, as no automated protection exists to enforce the previously valid condition. Active management and alignment with new operational requirements are crucial to avoid inconsistencies. Coordination with business stakeholders is essential to ensure alignment of data rules with organizational objectives .

CHECK constraints enforce data validation rules within the database. Ensuring a positive salary ensures all 'employee' data adheres to realistic business conditions, whereas ensuring a 'cgpa' above 7.5 in 'student' enforces academic standards. Both improve data quality and integrity. The difference lies in the nature of the constraints: numeric positivity vs. range compliance, which requires different validation logic .

Renaming columns or tables can improve clarity and relevance in database structure but can significantly impact application code, stored procedures, and any documentation referencing the old names. Updating these references is essential to maintain consistency and prevent runtime errors. Such changes should be carefully planned and documented as part of version control in application development .

To add a Foreign Key constraint linking the 'student' and 'dept' tables, first ensure that the primary key exists in the parent table, 'dept'. The 'student' table should have a column, say 'did', intended to be a foreign key. The datatype of the foreign key must match the referenced primary key column in 'dept'. Syntax for adding the constraint involves: ALTER TABLE student ADD CONSTRAINT fk_student_dept FOREIGN KEY (did) REFERENCES dept (did); This enforces data integrity by ensuring each 'did' in 'student' corresponds to an existing 'did' in 'dept' .

DEFAULT constraints automatically assign a value to a column if none is provided during data input, which helps maintain consistent data entries. Setting a default value like 'VSKP' for location in 'dept' streamlines data entry and reduces errors or data omissions. However, it could mask data entry errors if the default is not applicable, thus requiring careful initial setup .

Altering the column width for fields such as 'name' in the 'student' table can improve flexibility for storing longer data entries. However, it may increase storage requirements if not managed properly. The decision should consider expected data volume and performance implications. Appropriate adjustments should be supported by historical data trends and future usage forecasts .

Implementing UNIQUE constraints on columns like 'email-id' ensures data uniqueness, preventing duplicate entries which is crucial for identifying individual records and maintaining data integrity. Challenges include handling user errors during data entry and managing existing duplicates when adding constraints, which might require data cleanup or adjustment .

You might also like