Question

In: Computer Science

CREATE TABLE Hotel ( roomNumber     INTEGER         PRIMARY KEY, type                  CHAR(1

CREATE TABLE Hotel

(

roomNumber     INTEGER         PRIMARY KEY,

type                  CHAR(10)         NOT NULL,

rate                   INTEGER         NOT NULL,

--

CONSTRAINT IC1 CHECK (type IN ('suite', 'king', 'queen')),

CONSTRAINT IC2 CHECK (type <> 'suite' OR rate > 200),

CONSTRAINT IC3 CHECK (NOT (type = 'king' AND (rate < 80 OR rate > 220))),

CONSTRAINT IC4 CHECK (NOT (type = 'queen' AND rate >= 100))

);

which 8 of these inserts will be rejected only 8 are rejected

1.

INSERT INTO Hotel VALUES (21, 'king', 90);

2.

INSERT INTO Hotel VALUES (42, 'suite', 230);

3.

INSERT INTO Hotel VALUES (52, 'suite', 200);

4.

INSERT INTO Hotel VALUES (40, 'queen', 230);

5.

INSERT INTO Hotel VALUES (31, 'king', 50);

6.

INSERT INTO Hotel VALUES (30, 'queen', 50);

7.

INSERT INTO Hotel VALUES (22, 'suite', 90);

8.

INSERT INTO Hotel VALUES (10, 'queen', 210);

9.

INSERT INTO Hotel VALUES (20, 'queen', 90);

10.

INSERT INTO Hotel VALUES (51, 'king', 220);

11.

INSERT INTO Hotel VALUES (41, 'king', 230);

12.

INSERT INTO Hotel VALUES (32, 'suite', 50);

13.

INSERT INTO Hotel VALUES (11, 'king', 210);

14.

INSERT INTO Hotel VALUES (12, 'suite', 210);

15.

INSERT INTO Hotel VALUES (50, 'queen', 100);

Solutions

Expert Solution


1.
INSERT INTO Hotel VALUES (21, 'king', 90); -- not rejected

2.  
INSERT INTO Hotel VALUES (42, 'suite', 230); -- not rejected

3.  
INSERT INTO Hotel VALUES (52, 'suite', 200); -- rejected
Reason : The INSERT statement conflicted with the CHECK constraint "IC2". The conflict occurred in database "master", table "dbo.Hotel".

4.  
INSERT INTO Hotel VALUES (40, 'queen', 230); --rejected
Reason :The INSERT statement conflicted with the CHECK constraint "IC4". The conflict occurred in database "master", table "dbo.Hotel".

5.  
INSERT INTO Hotel VALUES (31, 'king', 50); --rejected
Reason : The INSERT statement conflicted with the CHECK constraint "IC3". The conflict occurred in database "master", table "dbo.Hotel".

6.  
INSERT INTO Hotel VALUES (30, 'queen', 50); -- not rejected

7.  
INSERT INTO Hotel VALUES (22, 'suite', 90); -- rejected
Reason : The INSERT statement conflicted with the CHECK constraint "IC2". The conflict occurred in database "master", table "dbo.Hotel".

8.  
INSERT INTO Hotel VALUES (10, 'queen', 210); -- rejected
Reason: The INSERT statement conflicted with the CHECK constraint "IC4". The conflict occurred in database "master", table "dbo.Hotel".

9.  
INSERT INTO Hotel VALUES (20, 'queen', 90); -- not rejected

10.  
INSERT INTO Hotel VALUES (51, 'king', 220); -- not rejected

11.  
INSERT INTO Hotel VALUES (41, 'king', 230); -- rejected
Reason :The INSERT statement conflicted with the CHECK constraint "IC3". The conflict occurred in database "master", table "dbo.Hotel".

12.  
INSERT INTO Hotel VALUES (32, 'suite', 50); -- rejected
Reason : The INSERT statement conflicted with the CHECK constraint "IC2". The conflict occurred in database "master", table "dbo.Hotel".

13.  
INSERT INTO Hotel VALUES (11, 'king', 210); -- not rejected

14.  
INSERT INTO Hotel VALUES (12, 'suite', 210); -- not rejected

15.  
INSERT INTO Hotel VALUES (50, 'queen', 100); -- rejected
Reason : The INSERT statement conflicted with the CHECK constraint "IC4". The conflict occurred in database "master", table "dbo.Hotel".


Related Solutions

create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key,...
create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node(node_id), foreign key (destination_id) references node(node_id)); write an SQL query that lists all those nodes that have edges with a destination node that has color 'red'.
Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table...
Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node(node_id), foreign key (destination_id) references node(node_id)); What is the result of the following query? select node_id, node_color, destination_id from node, edge; An inner join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node for all those nodes that have outgoing...
Create table, create primary and foreign key constraints. Create index on the table to satisfy a...
Create table, create primary and foreign key constraints. Create index on the table to satisfy a query with aggregate functions.
Consider the following SQL DDL statements: CREATE TABLE DEPT ( did INTEGER, dname VARCHAR(20), PRIMARY KEY(did));...
Consider the following SQL DDL statements: CREATE TABLE DEPT ( did INTEGER, dname VARCHAR(20), PRIMARY KEY(did)); CREATE TABLE EMP( eid INTEGER, name VARCHAR(20), did INTEGER, PRIMARY KEY(eid), FOREIGN KEY(did) REFERENCES DEPT); In the database created by above statements, which of the following operations may cause violation of referential integrity constraints? Question 1 options: UPDATE on DEPT INSERT into DEPT DELETE on EMP Both DELETE on EMP and INSERT into DEPT
1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint...
1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint 2.Use SQL to insert multiple records for each security classification with the same ID. You must have 4 classifications. 3.Use SQL to create 4 schemas, one for each security classification 4.Use SQL to create a view in each schema that restricts the records to those belonging to a particular security classification and restricts the columns to only those columns that have relevant data. 5.Select...
Using python. 1. How to create a dictionary that has an integer as a key and...
Using python. 1. How to create a dictionary that has an integer as a key and a byte as a value? the dictionary keys must contain integers from 0 to 255. It should be similar to UTF-8. When we enter an integer, it should return 1 byte. 2. Create a function when a user gives 1 byte, it should return the key from the previous dictionary.
Create MyArrayForString.java to handle 'String' type elements and create MyArrayForChar.java to handle 'char' type elements. Test...
Create MyArrayForString.java to handle 'String' type elements and create MyArrayForChar.java to handle 'char' type elements. Test them in MyArrayDemo.java. I need this in java on textpad. There are two files, both have instructions in them on what to add in the code. They are posted below. public class MyArrayForDouble { double[] nums; int numElements; public MyArrayForDouble() { // Constructor. automatically called when creating an instance numElements = 0; nums = new double[5]; } public MyArrayForDouble(int capacity) { // Constructor. automatically...
Create MyArrayForString.java to handle 'String' type elements and create MyArrayForChar.java to handle 'char' type elements. Test...
Create MyArrayForString.java to handle 'String' type elements and create MyArrayForChar.java to handle 'char' type elements. Test them in MyArrayDemo.java. I need this in java on textpad. There are two files, both have instructions in them on what to add in the code. They are posted below. I ONLY NEED TO TEST add() linearSearch() remove() and printArray(). public class MyArrayForDouble { double[] nums; int numElements; public MyArrayForDouble() { // Constructor. automatically called when creating an instance numElements = 0; nums =...
create table candidate ( cand_id   varchar(12) primary key,   -- cand_id name       varchar(40)           --...
create table candidate ( cand_id   varchar(12) primary key,   -- cand_id name       varchar(40)           -- cand_nm ); create table contributor ( contbr_id   integer primary key, name       varchar(40),           -- contbr_nm city     varchar(40),           -- contbr_city state       varchar(40),           -- contbr_st zip       varchar(20),           -- contbr_zip employer   varchar(60),           -- contbr_employer occupation   varchar(40)           -- contbr_occupation ); create table contribution ( contb_id   integer primary key, cand_id   varchar(12),           --...
Based on this code: CREATE TABLE DEPARTMENT ( DepartmentName Char(35) NOT NULL, BudgetCode Char(30) NOT NULL,...
Based on this code: CREATE TABLE DEPARTMENT ( DepartmentName Char(35) NOT NULL, BudgetCode Char(30) NOT NULL, OfficeNumber Char(15) Not Null, DepartmentPhone Char(12) NOT NULL, CONSTRAINT DEPARTMENT_PK primary key(DepartmentName) ); CREATE TABLE EMPLOYEE( EmployeeNumber Int NOT NULL AUTO_INCREMENT, FirstName Char(25) NOT NULL, LastName Char(25) NOT NULL, Department Char(35) NOT NULL DEFAULT 'Human Resources', Position Char(35) NULL, Supervisor Int NULL, OfficePhone Char(12) NULL, EmailAddress VarChar(100) NOT NULL UNIQUE, CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeNumber), CONSTRAINT EMP_DEPART_FK FOREIGN KEY(Department) REFERENCES DEPARTMENT(DepartmentName) ON UPDATE CASCADE, CONSTRAINT...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT