This document provides instructions for completing SQL and PL/SQL labs involving creating tables, inserting data, modifying data through DML statements, adding and modifying constraints, creating views and sequences, and granting privileges. The labs cover skills like creating tables with constraints, performing DML operations, using transactions with savepoints and rollbacks, modifying table structures with ALTER commands, creating views and sequences, and configuring user privileges.
This document provides instructions for completing SQL and PL/SQL labs involving creating tables, inserting data, modifying data through DML statements, adding and modifying constraints, creating views and sequences, and granting privileges. The labs cover skills like creating tables with constraints, performing DML operations, using transactions with savepoints and rollbacks, modifying table structures with ALTER commands, creating views and sequences, and configuring user privileges.
This document provides instructions for completing SQL and PL/SQL labs involving creating tables, inserting data, modifying data through DML statements, adding and modifying constraints, creating views and sequences, and granting privileges. The labs cover skills like creating tables with constraints, performing DML operations, using transactions with savepoints and rollbacks, modifying table structures with ALTER commands, creating views and sequences, and configuring user privileges.
This document provides instructions for completing SQL and PL/SQL labs involving creating tables, inserting data, modifying data through DML statements, adding and modifying constraints, creating views and sequences, and granting privileges. The labs cover skills like creating tables with constraints, performing DML operations, using transactions with savepoints and rollbacks, modifying table structures with ALTER commands, creating views and sequences, and configuring user privileges.
(student_id number primary key, student_name varchar2(30) not null, address varchar2(100), bdate date, tel varchar2(15) unique )
create table courses
(course_id number primary key, course_name varchar2(30) not null, credit_hour number )
create table students_courses
( course_id number references courses(course_id), student_id number references students(student_id), grade number check(grade between 0 and 100) , reg_date date, primary key(course_id,student_id) )
1 Insert one row in each of the following tables: students, courses and students_courses.
insert into students
values (1,'goondy','masr_station','17-JAN-1999','01014758445') insert into courses values (201,'math',3) insert into students_courses values (1,201,99.9,'1-OCT-2017')
2 Mark an intermediate point in the processing of the transaction.
Savepoint firsto
3 In the employees table increase the salary by 10% for employees whose salary is below 3000. update employees set salary=salary*1.1 where salary < 3000 ;
4 Empty (use delete not truncate) the students_courses table.
delete students_courses;
5 Discard the most recent DELETE and UPDATE operation without discarding the earlier INSERT operation.
Rollback to savepoint firsto
6 Make the data addition permanent.
commit
7 Add column 'Email' to table students and check that the email contains the @ sign.
alter table students
add (Email varchar2(50) check (Email like '%@%'))
8 Rename column tel to phone_no in the students table.
alter table students
rename column tel to phone_no
9 Modify the registration date column to have the current date as a default value.
alter table students_courses
modify reg_date default sysdate
10 Populate the students table with suitable values from the employees table.
11 Remove the constraint created in 7. Add another one which will assure that the email column has no duplicate values
alter table students drop constraint sys_c007010
alter table students add unique(Email) 12 Create the EMPLOYEES2 table based on the structure of the EMPLOYEES table. Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns. Name the columns in your new table ID, FIRST_NAME, LAST_NAME, SALARY, and DEPT_ID, respectively.
create table employees2 as
select employee_id as “ID”, first_name ,last_name ,salary ,department_id as “dept_id” from employees
13 Rename the EMPLOYEES2 table as EMP.
rename employees2 to emp
14 Create a view called EMP_VU based on the employee number, employee name, and department number from the EMP table. Change the heading for the employee name to EMPLOYEE.
create view EMP_VU
as select "id" , last_name , "dept_id" from emp
15 Select the view name and text from the appropriate data dictionary view.
16 Modify the EMP_VU view to display the employees in department 20.
Note: the view can't be used to manipulate the employees in departments other than 20.
create or replace view EMP_VU
as select "id" , last_name , "dept_id" from emp Where dept_id = 20
17 Attempt to reassign Fay to department 80 (using the created view).
ORA-01402: view WITH CHECK OPTION where-clause violation 18 Create a sequence to be used with the primary key column of the COURSES table. The sequence should start at 60 and have a maximum value of 200. have your sequence increment by ten numbers. Name the sequence COURSE_ID_SEQ.
Create sequence COURSE_ID_SEQ
Start with 60 Increment by 10 Max value 200
19 Insert one row in the courses table using the created sequence.
insert into courses
values (Course_id_seq.nextval, 'physics' , 3)
20 Display the following information about your sequences: sequence name,
maximum value, increment size, and last number.
21 Create a nonunique index on the foreign key column (DEPT_ID) in the EMP.
CREATE INDEX emp_dept_idx
ON emp("dept_id")
22 Display the index name and uniqueness that exist in the data dictionary for the EMP table.
23 Create a user with your name and give him the appropriate system privileges.
24 Create a role to include the appropriate object privileges the user needs to start properly. 25 Give the user the ability to see the contents of the students table. Create a synonym to facilitate the retrieval of the students table' contents.
