Assume the EMPLOYEE table contains four attributes, ID, DEPT, SALARY and NAME, and has the following rows ID DEPT SALARY NAME 100 SALES 40000 Smith 101 RD 38000 Terry 102 HR 60000 David 103 SALES 58000 Ellie 104 RD 70000 Judy The DEPARTMENT table contains two attributes, DEPTNAME and PHONE, and has the following rows: DEPTNAME PHONE RD 8596001234 SALES 8596005555 HR 8596009876 A.) The EMPLOYEE table needs to enforce the following integrity constraints: • The ID is the primary key. • The DEPT column is a foreign key that references the DEPTNAME in the DEPARTMENT table, so that any value that appears in the DEPT column should also exist in the DEPARTMENT table. • The value for the NAME column can never be null. What should be the statement used to create the EMPLOYEE table, if 1) the table was created as a partitioned table such that employees in different departments would be placed into different partitions, assuming the company only has three departments, i.e., SALES, HR and RD? 2) the table was created as a partitioned table, assuming the partitioning is based on the salary ranges as follows? • 0~30,000 • 30,001~50,000 • 50,001~80,000 • Above 80,000 3) the table was created as a partitioned table with 4 partitions, hoping that the numbers of rows in all partitions are approximately the same? B.) What is the statement to create a view EMP_V1, without check option, for employees whose salary falls into range 20,000~45,000? The view should have three columns: ID, NAME, and SALARY C.) After view EMP_V1 has been created successfully in b), what will be the query results for the following SQL statements? insert into emp_v1(id, name, salary) values(‘289’, ‘GAVIN’, 85000); select * from emp_v1; select * from employee where salary > 45000;
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Assume the EMPLOYEE table contains four attributes, ID, DEPT, SALARY and NAME, and has the following rows
ID DEPT SALARY NAME
100 SALES 40000 Smith
101 RD 38000 Terry
102 HR 60000 David
103 SALES 58000 Ellie
104 RD 70000 Judy
The DEPARTMENT table contains two attributes, DEPTNAME and PHONE, and has the following rows:
DEPTNAME PHONE
RD 8596001234
SALES 8596005555
HR 8596009876
A.) The EMPLOYEE table needs to enforce the following integrity constraints:
• The ID is the primary key.
• The DEPT column is a foreign key that references the DEPTNAME in the DEPARTMENT table, so that any value that appears in the DEPT column should also exist in the DEPARTMENT table.
• The value for the NAME column can never be null.
What should be the statement used to create the EMPLOYEE table, if
1) the table was created as a partitioned table such that employees in different departments would be placed into different partitions, assuming the company only has three departments, i.e., SALES, HR and RD?
2) the table was created as a partitioned table, assuming the partitioning is based on the salary ranges as follows?
• 0~30,000
• 30,001~50,000
• 50,001~80,000
• Above 80,000
3) the table was created as a partitioned table with 4 partitions, hoping that the numbers of rows in all partitions are approximately the same?
B.) What is the statement to create a view EMP_V1, without check option, for employees whose salary falls into range 20,000~45,000? The view should have three columns: ID, NAME, and SALARY
C.) After view EMP_V1 has been created successfully in b), what will be the query results for the following SQL statements?
insert into emp_v1(id, name, salary)
values(‘289’, ‘GAVIN’, 85000);
select * from emp_v1;
select * from employee where salary > 45000;
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
