write the full sql code(create table, insert...) Bonus table:| empId | bonus || 2| 4| 500| 2000Write an SQL query to report the name and bonus amount of each employee with a bonus less than1000.Return the result table in any order.The result should be:Output:| name | bonus I| Brad | null| John | null| Dan| 500 Create the following tables in your database with the following schema:Table: Employee| Column Name | Type| empIdI name| supervisor| salary| int| varchar || int| intempId is the primary key column for this table.Each row of this table indicates the name and the ID of an employee inaddition to their salary and the id of their manager.Table: Bonus| Column Name | Type || empId| bonus| int| intempId is the primary key column for this table.empId is a foreign key to empId from the Employee table.Each row of this table contains the id of an employee and their respectivebonus.Add the following data to your tables:Input:Employee table:| empId | name| supervisor | salary || 3| Brad| null| 4000| 1000| 2000| 4000| John| 3| 2| 4| Dan| 3| Thomas | 3

Answered: Image /qna-images/answer/08d8632c-0461-456c-9dc1-e4acbe682889.jpg

Answered: Write an SQL query to report the name…

Write an SQL query to report the name and bonus amount of each employee with a bonus less than 1000. Return the result table in any order. The result should be: Output: +------+ | name | bonus |

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter13: Views

Section: Chapter Questions

Problem 3MC

See similar textbooks

Similar questions

Do the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key. FIGURE P3.17 THE CH03_TRANSCO DATABASE TABLES
Using MySQL, design a 2 table database. The data types of columns should be set properly tblMembers The tblMembers will be used to store the details of the Gym Members MemberID, Name, Surname, DateOfBirth, ID,Contact, DateJoined MemberType (Annual: R200/year, Monthly: R50/month) The tblPayments table will be used to keep track of payments made by Members. PaymentID, Date, Amount. The tblPayments should be related to tblMembers accordingly Using Java and the jdbc API , create a startup frame that will be used navigate the application. The frame should have 3 buttons. 1.4 The first button will open aa frame that can be used to add, update and delete Gyjm Members. The second button will open a frame that will be used to collect the payments made by students. 1.5 The third button will display a frame that calculates the current age and the balances, and displays a summary report in a table, as follows for an example: Order # is the sequence number of the member in tblMembers…
3.1 Create a database Cat with the following schema: CREATE TABLE owners ( ownerid integer, name text, age integer, PRIMARY KEY (ownerid) ); CREATE TABLE cats ( catid integer, owner integer, name text, breed text, age integer, PRIMARY KEY (catid), FOREIGN KEY (owner) REFERENCES owners); 3.2. Write a SQL query that returns the number of cats, over the age of 10, of each breed of cat. 3.3. Write a SQL query that returns the ownerid/owner of the one cat owner who owns the most cats.
This question relates to creating and altering tables, as well as implementing constraints and referential integrity. Q.1.1 You are required to create the following tables in a database named SICKLEAVE:EMPLOYEESEMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEYEMPLOYEE_NAME VARCHAR(30) NOT NULLEMPLOYEE_SURNAME VARCHAR(30) NOT NULLDATE_OF_BIRTH DATE NOT NULLDOCTORSDOCTOR_ID VARCHAR(5) NOT NULL PRIMARY KEYDOCTOR_NAME VARCHAR(30) NOT NULLEMPLOYEE_SICKLEAVEEMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEYFOREIGN KEY REFERENCES EMPLOYEES(EMPLOYEE_ID)START_DATE DATE NOT NULL PRIMARY KEYDOCTOR_ID VARCHAR(5) NOT NULLNUMBER_OF_DAYS SMALLINT NOT NULLQ.1.2 Populate the tables created in Q.1.1 with the following data:EMPLOYEESEMPLOYEE_ ID EMPLOYEE_NAME EMPLOYEE_SURNAMEDATE_OF_BIRTHI0001 Dominique Woolridge 1993-04-19I0002 Nico Baird 1991-11-19I0003 Derek Moore 1992-06-24I0004 Neo Petlele 1993-12-29I0005 Andrew Crouch 1994-01-30DOCTORS DOCTOR_ID DOCTOR_NAMED0001 Thabo NtlaliD0002 Deon CoetzeeD0003 Kwezi…
Part A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.
Part A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: Find the sids of suppliers who supply only red parts. Find the sids of suppliers who supply a red part and a green part. Find the sids of suppliers who supply a red part or a green part. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies. For every supplier that supplies a green part and a…
Part A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: Find the pnames of parts supplied by Acme Widget Suppliers and no one else. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part). For each part, ﬁnd the sname of the supplier who charges the most for that part. Find the sids of suppliers who supply only red parts. Find the…
Part A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: Find the pnames of parts for which there is some supplier. Find the snames of suppliers who supply every part. Find the snames of suppliers who supply every red part. Find the pnames of parts supplied by Acme Widget Suppliers and no one else. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers…
Use Microsoft SQL Server Management Studio for this practical i. Create a database called ‘ORG’ ii. create and populate the following tables: First table CREATE TABLE Worker (WORKER_ID INT NOT NULL PRIMARY KEY, FIRST_NAME CHAR(25), LAST_NAME CHAR(25), SALARY INT, JOINING_DATE DATETIME, DEPARTMENT CHAR(25) ); 2INSERT INTO Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES (001, 'Monika', 'Arora', 100000, '2014-02-20 09:00:00', 'HR'),(002, 'Niharika', 'Verma', 80000, '2014-06-11 09:00:00', 'Admin'),(003, 'Vishal', 'Singhal', 300000, '2014-02-20 09:00:00', 'HR'),(004, 'Amitabh', 'Singh', 500000, '2014-02-20 09:00:00', 'Admin'),(005, 'Vivek', 'Bhati', 500000, '2014-06-11 09:00:00', 'Admin')); Second table CREATE TABLE Bonus (WORKER_REF_ID INT,BONUS_AMOUNT INT,BONUS_DATE DATETIME,FOREIGN KEY (WORKER_REF_ID) REFERENCES Worker(WORKER_ID)); INSERT INTO Bonus (WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES(001, 5000, '2016-02-20'),(002, 3000,…
Consider the following relational schema and answer the following questions. Employee (Employee_Id, First_Name, Last_Name, Email, Phone_Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id, Department_Id) Write a query to get the difference between the highest and lowest salaries. Write a query to find the manager ID and the salary of the lowest-paid employee for that manager. Write a query to get the department ID and the total salary payable in each department. Write a query to get the total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only. Write a query to get the average salary for all departments employing more than 10 employees.
Use the information below to answer the question. What types of additional information and constraints would you like to represent in the schema? Think of several users of your database, and design a view for each. CREATE TABLE USER1 (SSN INT NOT NULL, FNAME VARCHAR2(20), M VARCHAR(1), LNAME VARCHAR2(20), ADDRESS VARCHAR2(100), PHONE INT, AGE INT, SEX VARCHAR(1), JOBTYPE VARCHAR2(30), ASTYPE VARCHAR2(30), ETYPE VARCHAR2(30), SHIFT VARCHAR2(20), POSITION VARCHAR2(30), AP_NAME VARCHAR2(100), PRIMARY KEY(SSN), FOREIGN KEY(AP_NAME) REFERENCES AIRPORT(AP_NAME) ON DELETE CASCADE); INSERT INTO USER1(SSN, FNAME, M, LNAME, ADDRESS, PHONE, AGE, SEX, JOBTYPE, ASTYPE, ETYPE, SHIFT, POSITION, AP_NAME) VALUES(123456789,'LINDA','M','GOODMAN','731 Fondren, Houston,…
Given the Bank database that contains the following tables (primary keys are underlined and foreign keys are preceded with #):Agency(agencyNumber, agencyName, city, assets)Client (clientNumber, firstName, lastName, clientCity)Account(accountNumber, #agencyNumber, #clientNumber, balance)Loan(loanNumber, #agencyNumber, #clientNumber, amount ) c) Find the average balance of customer accounts, for each agency whose average balance is greater than 10,000. The table result should have the columns agencyNumber and Balance_Average

SEE MORE QUESTIONS

Recommended textbooks for you

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS