question 8 please use the picture below to answer the following question Write a SQL statement to modify the Position table in order to define a constraint to ensure that the column position receives only values 'Sales Rep' or 'Manager'.
Q: In PL SQL, Create a trigger on employees that will not allow employees to update salary for…
A: To create a trigger that restricts updating accountant salary from employee table.
Q: If a GROUP BY clause is included in a SQL query, the characteristics that may be sought in the…
A: In SQL ,If a select block does have a GROUP BY clause, any column specification specified in the…
Q: Write the SQL command for the following statement based on the Figure 1. a) Drop the constraint…
A: A.ALTER TABLE PropertyForRent DROP CONSTRAINT PropertyForRent_branchNo_fk; B.RENAME TABLE…
Q: Create both the tables Branch and Supervisor with appropriate attribute names, primary key, foreign…
A: Note: - As per the guidelines we can only answer maximum of three subparts. Please resubmit the…
Q: Create a procedure, ADD_EMPLOYEE., to insert a new employee into the EMPLOYEES table. The procedure…
A: Add another employee named Joe Harris in department 80. leaving remaining parameters with their…
Q: Write the SQL code that will create only the table structure for a table named EMP_1. This table…
A: SQL commands to create a table: CREATE TABLE: This command is used to create a table structure in…
Q: CREATE SEQUENCE ORD_SEQ Increment by 10 Start with 120 Maxvalue 9999 Nocycle; CREATE TABLE…
A: Nearby answer is : 1 and 3 for, 1 : An error will occur because you cannot use SYSDATE in the…
Q: 3. Create a table for student mark list and insert the values for name, id, m1,m2,m3,m4,m5, total…
A: Oracle SQL: Oracle SQL Developer is a free, integrated development environment that streamlines…
Q: Define three types of join commands and use SQL to write these commands.
A: Inner join: It is used to find the matching value from both the tables. Query: SELECT…
Q: Display the information about the managers and clerks from the column ‘Job’ in the table emp, order…
A: Answer: SELECT * FROM emp WHERE Job=="managers" OR Job=="clerks" ORDER BY deptno;
Q: Select clause and from clause: Display the columns specified above in the problem statement from…
A: Write a query to display the author ID, first name, last name, and year of birth for all authors…
Q: Write in SQL a command that creates the table Customers according to the above description (2…
A: SQL answers to the above first 3 questions are in step2 as per our guidelines we are supposed to…
Q: Write a SQL statement to create a foreign key in table Invoice_Detail that refers to the…
A: Write a SQL statement to create a foreign key in table Invoice_Detail that refers to the…
Q: Q2/ The difference between the results of executing the following two SQL commands: delete from…
A: Answer to the given question: delete from Employee: will delete all the tuples from the Employee…
Q: Create a procedure, ADD_EMPLOYEE., to insert a new employee into the EMPLOYEES table. The procedure…
A: Call ADD_EMPLOYEE for the name Jane Harris in department 15. leaving other parameters with their…
Q: Consider Following Tables. Student ( regNo, sName, sCity, sCGPA, sAge) Course ( cCode, cName,…
A: note: we are only allowed to solve maximum of three sub-part in one question. Please post other part…
Q: Explain the purpose of the CHECK clause within a CREATE TABLE SQL command. Explain the purpose of…
A: Given: Explain the purpose of the CHECK clause within a CREATE TABLE SQL command. Explain the…
Q: Write Create Table SQL statements based on the above ERD. All tables must have primary keys. All…
A: Big Data SQL: Big data SQL or structured query language can be regarded as a base or medium of…
Q: Use Column level constraint setting while designing the below given table in sql named as…
A: Column constraints: In Structured Query Language, the column level constraints are used to specify…
Q: Write the set of SQL commands necessary to insert the data into the CUSTOMER table
A: NAME: CUSTOMER COLUMNS: CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_BALANCE
Q: Create a SQL query that selects OrderID and OrderDate from the Orders database and OrderDetailID…
A: Definition A JOIN clause is used to join data from more than "2" tables together based on a common…
Q: Create a SQL query leveraging the institution's schema to determine who has not taken a course at…
A: Query in SQL : The Structured Query Language (SQL) query below lists all students' IDs who have not…
Q: use the following picture to answer the question below Assuming Order table already exist, add a…
A: To add a Foreign Key Constraint to an already existing table, the below query syntax is used. ALTER…
Q: question 6 please use the picture below to answer the following question Write a SQL statement to…
A: Explanation: “UNIQUE” constraint is used to ensure that the column having different values. The…
Q: Question 2: SQL Please see the following tables. Solve the questions with nested queries and…
A: SELECT Sailors.rating, AVG(Sailors.age) AS average_age FROM Sailors GROUP BY Sailors.rating HAVING…
Q: Having created the table structure in Problem 1, write the SQL code to enter the first two rows for…
A: Having created the table structure in Problem 1, write the SQL code to enter the first two rows…
Q: EMPLOYEE, and PROJECT tables using common attributes. Sort the results by project value. In the…
A: Given:- Using the EMPLOYEE, JOB, and PROJECT tables in Figure P7.1, write the SQL code that will…
Q: Modify the following SQL command so that the Rep_ID column is the PRIMARY KEY for the table and the…
A: Actually, SQL stands for Structured Query Language.
Q: Write an SQL CREATE TABLE statement to create the PET_OWNER table, withOwnerID as a surrogate key.…
A: Given:- Write an SQL CREATE TABLE statement to create the PET_OWNER table, withOwnerID as a…
Q: . Please write SQL codes to query data from member tables based on below criteria Please write a…
A: A number is odd number if its remainder when divided by 2 is not equal to 0
Q: The given SQL creates a Movie table and inserts some movies. The SELECT statement selects all…
A: Answer
Q: Create a function to insert a new product into an existing order, include the product id, unit…
A: PL / SQL: PL / SQL stands for Process Language / Structured Language in Question. It serves as a…
Q: Write an appropriate SQL query to determine if there are any students that are not registered for…
A: Given: Write an appropriate SQL query to determine if there are any students that are not registered…
Q: Create a procedure, ADD_EMPLOYEE., to insert a new employee into the EMPLOYEES table. The procedure…
A: Create the ADD_EMPLOYEE procedure to add an employee to the EMPLOYEES table.The row should be added…
Q: CREATE TABLE Employee( firstName char(30) , lastName char(30) , hireDate date ,empNo int ,…
A: The table "Employee" with fields "firstName, "lastName", "date", "empNo", empInitial, and "years" is…
Q: Write Create Table SQL statements based on the above ERD. All tables must have primary keys. All…
A: Given diagram contains entities Book, Allocation and Author. These entities attributes are: Book-…
Q: Show the structure of the EMP table. Create a query to display the name, job. hire date, and…
A: SELECT command is used to display the details specified column names
Q: The Student table contains marks of 10 students belong to different sections Create the following…
A: Dear Student, To create the table we will use the CREATE instruction of SQL. So, SQL query will be…
Q: Create a SQL statement for the CLIENTS table using the table structure below: Table Name:…
A: The syntax of creating the table is given by CREATE TABLE table_name ( column1 datatype,…
Q: Consider Following Tables. Student ( regNo, sName, sCity, sCGPA, sAge) Course ( cCode, cName,…
A: Given: Consider Following Tables. Student ( regNo, sName, sCity, sCGPA, sAge) Course ( cCode, cName,…
Q: Define the term referential integrity constraint. Give an example of a referential integrity…
A: Note: There are multiple questions are given in one question. According to the rule, you will get…
Q: PLZ hlp with the following: In SQL If you add the PRIMARY KEY constraint to a column, you must…
A: SQL is a standard language for storing, manipulating and retrieving data in databases. SELECT -…
Q: QUESTION 4 constraint may be used to place a restriction on a column to ensure that no duplicate…
A: NO duplicate value means all the values in the column are different
Q: Because of referential integrity, before any row can be entered into the SECTION table, the CourseID…
A: Referential integrity is one type of range control in which the value of that field should be filled…
Q: Question No. 4:…
A: 1-Display names of all those students who have "A" grades: We must join Student to display student…
question 8
please use the picture below to answer the following question
Write a SQL statement to modify the Position table in order to define a constraint to ensure that the column position receives only values 'Sales Rep' or 'Manager'.
Step by step
Solved in 2 steps
- CUSTOMERColumns = 7, Rows = 10 CustomerID LastName FirstName Address ZIP Phone Email 1 Shire Robert 6225 Evanston Ave N 98103 206-524-2433 Robert.Shire@somewhere.com 2 Goodyear Katherine 7335 11th Ave NE 98105 206-524-3544 Katherine.Goodyear@somewhere.com 3 Bancroft Chris 12605 NE 6th Street 98005 425-635-9788 Chris.Bancroft@somewhere.com 4 Griffith John 335 Aloha Street 98109 206-524-4655 John.Griffith@somewhere.com 5 Tierney Doris 14510 NE 4th Street 98005 425-635-8677 Doris.Tierney@somewhere.com 6 Anderson Donna 1410 Hillcrest Parkway 98273 360-538-7566 Donna.Anderson@elsewhere.com 7 Svane Jack 3211 42nd Street 98115 206-524-5766 Jack.Svane@somewhere.com 8 Walsh Denesha 6712 24th Avenue NE 98053 425-635-7566 Denesha.Walsh@somewhere.com 9 Enquist Craig 534 15th Street 98225 360-538-6455 Craig.Enquist@elsewhere.com 10 Anderson Rose 6823 17th Ave NE 98105 206-524-6877 Rose.Anderson@elsewhere.com EMPLOYEEColumns = 5, Rows = 5 EmployeeID LastName FirstName…CUSTOMERColumns = 7, Rows = 10 CustomerID LastName FirstName Address ZIP Phone Email 1 Shire Robert 6225 Evanston Ave N 98103 206-524-2433 Robert.Shire@somewhere.com 2 Goodyear Katherine 7335 11th Ave NE 98105 206-524-3544 Katherine.Goodyear@somewhere.com 3 Bancroft Chris 12605 NE 6th Street 98005 425-635-9788 Chris.Bancroft@somewhere.com 4 Griffith John 335 Aloha Street 98109 206-524-4655 John.Griffith@somewhere.com 5 Tierney Doris 14510 NE 4th Street 98005 425-635-8677 Doris.Tierney@somewhere.com 6 Anderson Donna 1410 Hillcrest Parkway 98273 360-538-7566 Donna.Anderson@elsewhere.com 7 Svane Jack 3211 42nd Street 98115 206-524-5766 Jack.Svane@somewhere.com 8 Walsh Denesha 6712 24th Avenue NE 98053 425-635-7566 Denesha.Walsh@somewhere.com 9 Enquist Craig 534 15th Street 98225 360-538-6455 Craig.Enquist@elsewhere.com 10 Anderson Rose 6823 17th Ave NE 98105 206-524-6877 Rose.Anderson@elsewhere.com EMPLOYEEColumns = 5, Rows = 5 EmployeeID LastName FirstName…A unique identifier for a data row that consists of more than one field is commonly called a: primary plus key composite primary key foreign key none of the above
- PK Cus CODE PK INV NUMBER PK,FK1 INV NUMBER LINE NUMBER HEDenerates contains PK CUS LNAME CUs FNAME CUs INITIAL CUS AREACODE CUS PHONE CUS BALANCE FK1 CUS CODE INV DATE P CODE LINE UNITS LINE PRICE FK2 is found in VENDOR PRODUCT PK V CODE PK P CODE V NAME v CONTACT V AREACODE V PHONE V_STATE V ORDER P DESCRIPT P INDATE P_QOH P MIN P PRICE DISCOUNT FK1 V CODE upplies Write a SQL query to list the product code, vendor code, and vendor name for all products and includes those vendors with no matching products. Y CODE- PRODUCTY CODECONSIDER THE INVOICE TABLE BELOW: invoice id NUMBER (12), invoice date DATE,Invoice total NUMBER (9, 2), payzent_total NUBER (9, 2), credit total NUMBER (9, 2),invoice due date DATE, payrent date DATE,CIL REC user VARCHAR2 (12),CTL SEC level NUMBER (1)Consider the INVOICE table structure below: A trigger is created on the INVOICE table: CREATE OR REPLACE TRIGGER rg_On_Quiz before insert or update on Invoice FOR EACH ROW DECLARE V_level Number; V_name VARCHAR (20); Begin SELECT USERNAME, SECURITY_LEVEL INTO V_name, V_level FROM AUTHORIZED_USERS WHERE USERNAME = USER; :NEW.CTL_REC_user := V_name; :NEW.CTL_REC_user := V_level; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-2003, 'UNAUTHORIZED USER'); END; In Oracle/PLSQL, the USER function returns the user_id from the current Oracle session. a.) Explain what this triger does…student takes ID ID course_id sec_id semester year grade пате dept_name tot_cred section course department course_id sec_id semester year building room_no time_slot_id course_id title dept_name credits advisor dept_name building budget s_id i id time_slot time_slot_id day start_time end_time prereq instructor classroom COurse_id prereg_id ID building пате dept_name salary room_no сараcity teaches ID course_id sec_id semester year Consider the relation that results from the expression: course natural join department Which of the following functional dependencies hold for this relation? (Mark all that apply) O {title, credits} -> building O dept_name -> budget O course_id -> {budget} O {building, budget} -> dept_name O title -> credits O {course_id, title} -> credits
- MySql Workbench CREATE TABLE students ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT, major VARCHAR(50), faculty VARCHAR(50)); CREATE TABLE location ( id INT PRIMARY KEY, name VARCHAR(50), rooms INT); CREATE TABLE faculty ( id INT PRIMARY KEY, name VARCHAR(50), department_id INT); 1. List last name of all students whose first name is longer than 4 letters in ascending order accordingto the last name. Duplicated rows should be removed from the output.2. Count the total number of rooms in Location.3. Find the number of students in each major.4. Find the number of employees in each department who get no commission or have salary less than5000.5. Find the maximum salary of employees in each department that the employee was hired 15 yearsbefore now. *hint: user TIMESTAMPDIFF(<unit type>,<Date_value 1>,<Date_value 2>), the unitcan be YEAR, MONTH, DAY, HOUR, etc...Indicate the changes you need to make to the KimTay Pet Supplies database to support the following additional requirement. Each location has a manager who is identified by a manager ID, a manager first name, and a manager last name.ERD:- PK CHECKOUT Check Num FK1 Book_Num FK2 Pat_ID Check Out Date Check_Due Date Check_In_Date PK BOOK Book Num Book Title Book_Year Book_Cost Book_Subject FK1 Pat_ID -H- PATRON PK Pat ID Pat_FName Pat_LName Pat_Type WRITES PK,FK1 Book Num PK,FK2 Au ID AUTHOR PK AU ID Au_FName Au_LName Au_BirthYear
- Name the candidate keys in the following table: Student L number Student Name Student_SS_Number Student_Address Student City Student State LO005485 Leigh 811-47-4449 5744 KENSINGTON Ave. Boston MD LO004812 Tammy 441-79-7448 30936 OAK Alley Colorado Springs TX LO004415 Alfredo 375-78-2633 5115 BOWEN St. Kansas City VT LO003478 Ramona 360-82-2835 5097 HUBBARD LOWER Blvd. Miami PA LO003058 Crystal 528-16-2635 73192 RANDOLPH SUB Dr. Riverside MD LO004588 Angie 765-16-4433 5115 GROVELAND PARK Dr. Cincinnati FL L0005484 Tonya 410-76-3632 5115 BISHOP FORD Blvd. Irving MS LO003161 Preston 451-47-2634 95289 MONROE Ave. Boston NC HTM B I A - A - I E E E E E X X, E E D T T 12pt ParagraphCustomers ld Username Firstname Sumame Email PostalAddress Orders Id Customorld Created Status Orderitems Orderid StyleRequested SizeRequested Stockid Shirts Stockld Style Size StoreLocation Count Which of the following statements are correct for this entity rel ionship diagram? a. There is a many to many relationship between Orderitems and Orders Ob. There is a many to one relationship between Customers and Orders c. Orderltems is a bridge table Od. There is a many to one relationship between Orderitems and Shirts □e. Orders is a bridge table Of. There is a many to many relationship between Orderltems and ShirtsGiven the Employee, Class, and Training tables. Employee Emp_id (PK) EM8765 Address USA Name Phone Email Ali ali@yahoo.com nazire@gmail.com katrina@gmail.com lemi@hotmail.com alican@yahoo.com mustafa@hotmail.com hasan@gmail.com 98733211 ЕМ3456 Nazire Dubai 98764533 90857464 EM7658 Katrina USA EM1987 Lemi USA 99663425 EM8009 Alican UK 95583372 |EM7698 Mustafa Dubai Jordan 92234452 EM6666 Hasan 92123366 Class Class_id (PK) Class_name Multimedia Credit СOM106 COM206 Database сомз06 Public Speaking 9. Training Table Emp_id (FK) EM8765 Date Class_id (FK) Result 05-May-2010 08-May-2011 08-Feb-2010 08-Feb-2010 соM106 Pass EM3456 COM206 Pass EM7658 сомз06 Fail EM1987 сомзо6 Fail 28-Jan-2011 10-March-2012 EM8009 сомз06 Pass EM7698 сом106 Fail EM8765 08-May-2011 сOM206 Pass Write the appropriate SQL queries a) Get the address of employee whose name is Alican b) Get the name and email of employees located in Dubai. Sort the results in alphabetical order of name. c) Show the count of employees…