SET Operations in Oracle are supported by following commands :-
1. UNION - Combine results of two or more Select statements but remove duplicate rows.
2. UNION ALL - Combine results of two or more select statements but show duplicate rows.
3. INSTERSECT - Combine results of two or more select statements but only show rows common in both statements.
4. MINUS - Combine results of two or more select statements but only show those in the final results that are present only in the first set of result.
SQL COMMANDS
Step 9.
CREATE TABLE LFM_MoreSkill (
Skillcode varchar(50),
Skillname varchar(100),
UNIQUE(Skillname),
PRIMARY KEY(Skillcode)
);
INSERT INTO LFM_MoreSkill VALUES('BD','Build Databases');
INSERT INTO LFM_MoreSkill VALUES('DD','Design Databases');
INSERT INTO LFM_MoreSkill VALUES('PP','PL/SQL Programming');
INSERT INTO LFM_MoreSkill VALUES('ML','Machine Learning');
INSERT INTO LFM_MoreSkill VALUES('PM','Pattern Matching');
STEP 10.
10a.
SELECT * FROM MoreSkill
MINUS
SELECT * FROM Skill
10b.
SELECT * FROM Skill
MINUS
SELECT * FROM MoreSkill
10c.
SELECT * FROM Skill
INSTERSECT
SELECT * FROM MoreSkill
10d.
SELECT * FROM Skill
UNION
SELECT * FROM MoreSkill
NOTE - For any other query, Pls use the comment section.
Oracle SQL Step 9. Create the table below and insert data LFM MoreSkill table SkillcodeSkillname Primary...
create table sql query and insert data sql query for the below - VehicleType(make, model, doors, body,trim) Vehicle(regNum, fleetNum, colour, make,model) ScheduledService(date,kilometrage,regNum,depotId) ServiceHistory(date,cost,description,regNum,depotId) Records(make,model,tariffId,rentalPrice) DailyTariff(tariffId, conditions) Invoice(invoiceId,qualityCheck,datePaid,finalCost,date) Insurance(insuranceId, policyType, cost) Insures(insuranceId,date,policyNumber) HiredVehicle(date, cardType, cardNo, kilometrage, days,tariffId,clientId,depotId,regNum) Depot(depotId, street,postcode,phone,fax) Booking(startDate,hiredays,colour,depotId,make,model,clientId) Client(clientId,street,postcode,phone) PersonalClient(clientId, fName, lName, title, driversNum) CompanyClient(clientID, cname) underlined are primary keys and italicised are foreign keys.
Using the Table and data below, create a procedure that accepts product ID as a parameter and returns the name of the product from ProductTable table. Add exception handling to catch if product ID is not in the table. Please use Oracle SQL and provide screenshot. Thanks! CREATE TABLE ProductTable( ProductID INTEGER NOT NULL primary key, ProductName VARCHAR(50) NOT NULL, ListPrice NUMBER(10,2), Category INTEGER NOT NULL ); / INSERT INTO ProductTable VALUES(299,'Chest',99.99,10); INSERT INTO ProductTable VALUES(300,'Wave Cruiser',49.99,11); INSERT INTO ProductTable...
I am using Oracle SQL and am new to it. I have seven tables, one
of them is a subtable of two of the others. I need to do the
following queries:
1. List all Patients
and what Bed they are assigned to
2. List all patients
who had Treatments and what Treatment they received
3. List all patients
who had tests and what Test they had
4. List the employees
(doctors, nurses, etc.) who assisted each patient.
5. List...
Use the SQL statements provided to create your tables. Write one
SQL statement for each of the following problems. You can only use
the conditions listed in the tasks. E.g., in task 1, you cannot
manually look up pid of Information Systems undergraduate
program.
Here is the given code:
drop table textbook_schedule cascade constraints;
drop table textbook_author cascade constraints;
drop table schedule cascade constraints;
drop table course cascade constraints;
drop table textbook cascade constraints;
drop table author cascade constraints;
drop...
SQL
I have a database
CREATE TABLE vendor
( vid CHAR(2) NOT NULL,
vname VARCHAR(25) NOT NULL,
PRIMARY KEY (vid) );
CREATE TABLE category
( catid CHAR(2) NOT NULL,
catname VARCHAR(25) NOT NULL,
PRIMARY KEY (catid) );
CREATE TABLE product
( pid CHAR(3) NOT NULL,
pname VARCHAR(25) NOT NULL,
price NUMERIC (7,2) NOT NULL,
vid CHAR(2) NOT NULL,
categoryid CHAR(2) NOT NULL,
PRIMARY KEY (pid));
CREATE TABLE region
( rid CHAR NOT NULL,
rname VARCHAR(25) NOT NULL,
PRIMARY KEY (rid)...
1. Create a PL/SQL program block that determines the top students with respect to GPA. Assume that the database has four tables. Student(SSN, SName, DOB, Major) , Grade(SSN, CNo, Grade(0,1,2,3,4)) and Course table(CNo,CName, Credit Hour), Prerequisite(CNo, PreCNo); Student and couse data ae given in the following SQL statements a. Accept a number n as user input with SQL*Plus telling top n%. b. In a loop get the SName and GPA of the top n% people with respect to GPA. c....
Problems and Exercises 1 through 9 are based on the dass scheduling
3NF relations along with some sample data shown in Figure 6-11.
Not shown in this figure are data for an ASSIGNMENT relation,
which represents a many-to-many relationship between faculty and
sections.4. Write SQL data definition commands for each of the following queries:a. How would you add an attribute, Class, to the Student
table?b. How would you remove the Registration table?c. How would you change the FacultyName field from...
Step 1: Create table audits via triggers The system must log any insertion, deletion, or updates to the following tables: • Employee table (project 1) create table Employee ( empNumber char(8) not null, firstName varchar(25) null, lastName varchar(25) null, ssn char(9) null, address varchar(50) null, state char(2) null, zip char(5) null, jobCode char(4) null, dateOfBirth date null, certification bit null, salary money null, constraint PK_EMP PRIMARY KEY(empNumber), constraint EMP_STATECHECK CHECK(state in ('CA','FL')) ) GO • Job table (project 1) create...
--- Paper Review drop table paper_review cascade constraint; drop table paper_author cascade constraint; drop table paper cascade constraints; drop table author cascade constraints; drop table reviewer cascade constraints; create table reviewer ( rid int, --- reviewer id rname varchar(50), --- reviewer name remail varchar(50),-- reviewer email raffiliation varchar(50),-- reviewer affiliation primary key (rid) ); insert into reviewer values(1,'Alex Golden', '[email protected]','UMBC'); insert into reviewer values(2,'Ann Stonebraker', '[email protected]','UMD'); insert into reviewer values(3,'Karen Smith', '[email protected]','UMB'); insert into reviewer values(4,'Richard Wallas', '[email protected]','UMBC'); insert into...
Overview: Database management plays an integral role in nearly every area of business. Databases house customer, accounting, and employee data, and these different data sets must all be efficiently managed in order to make the data accessible. Companies rely on database engineers to ensure that their records are accurate, updated, and tracked in real time. This course covers structured query language (SQL) and how it can be used to manage database schemas, manipulate data, and analyze data. For your final...