help with sample exam for etra ponints..SQL #1. a. Create a parts table with the

Question

help with sample exam for etra ponints..SQL

#1. a. Create a parts table with the following information:

PartId primary

key

PartDesc

PartPrice

b. Create an orders table with the following fields:

OrderId primary

key

OrderDate

PartId foreign

key

OrderQty

Status

c. Use appropriate data types and CONSTRAINTS, while creating tables.

Note: I realize you might answer this in Part a and b.

d. How do you check, if the constraints have been added or not.

#2. Insert values in the Orders and Part table, by prompting the user

to input the values:

Part table 10

Stapler 4

20 Binder 1.50

Order table 1

5MAY02

20 10 Paid

#3. Now create a query, which will return the output given below:

a. Make sure the format of the date returned is MMDDYY

b. PartDesc shows only first 10 characters

c. Column headings are displayed as in the output

OrderDate PartDesc PartId Quantity Unit

Price Total Price

010502

Binder 20 10 1.50

15.00

#4. a. Create a view ORDER_V, with the following fields, for the part

"Binder":

OrderId

PartDesc

OrderQty

b. What are correlated subqueries. Why are views used?

#5.

a. Why are indexes used on fields in the table?

b. In general, on which fields should you use indexes on?

c. On which fields should you avoid using indexes on?

d. Do indexes take up space in the database?

e. How do you display indexes and uniqueness that exist in the data

dictionary for the EMP table or any table for that matter?

#6.Create a SQL statement, that takes the employee number from the

user and displays the following:

EMPNOENAME SAL Hiredate First Monday 6mths after hiredate

100

John 1200 010202

070802

#7. Use joins to show the department number, dept. name , number of

employees and average salary of all departments together with the

names, salaries and jobs of the employees working in that department.

EMP TABLE:

Name Null? Type

ENAME

VARCHAR2(20)

SAL NUMBER(7, 2)

JOB VARCHAR2(9)

EMPNO NOT NULL NUMBER(4)

DEPTNO NOT NULL NUMBER(2)

DEPT TABLE:

Name Null? Type

DEPTNO

NOT NULL NUMBER(2)

DNAME CHAR(14)

LOC CHAR(13)

#8. List four ways to improve query performance?

Explanation / Answer

I'm pretty sure with 7 and 8 so here is my work any issue with that please comment

7)

select summary_view.deptno "Dept.no",
summary_view.dname,
summary_view."No.of Emps",
decode(summary_view.avg_sal,0,'no average',to_char(round(summary_view.avg_sal,2),'999999.99')) "Avg Salary",
e.ename,
e.job
from(select d.deptno deptno,
d.dname dname,
avg(nvl(e.sal,0)) avg_sal,
count(e.empno) "No.of Emps"
from emp e,
dept d
where d.deptno=e.deptno(+)
group by d.deptno,
d.dname) summary_view,
emp e
where e.deptno(+)=summary_view.deptno;

8)
>Avoid Multi-statement Table Valued Functions (TVFs). Multi-statement TVFs are more costly than inline TVFs
> Avoid use of Non-correlated Scalar Sub Query. Use this query as a separate query instead of part of the main query and store the output in a variable, which can be referred to in the main query or later part of the batch
>Use UNION ALL in place of UNION if possible.
>Most selective columns should be placed leftmost in the key of a non-clustered index
>Use TABLOCKX while inserting into a table and TABLOCK while merging.
>Use WITH (NOLOCK) while querying the data from any table
>Drop unused Indexes.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.