Please help me come up with queries that work with the given database I attach just be sure to take screenshots of output and code and execution plan  , you can do this in either my sql server or microsoft management studio.

This part will cover exploration of SQL query alternatives for selected problem patterns. You can user both SQL Server and Oracle (in case of differ possible syntax) but to count each query must be true alternative meaning their execution/explain plans must be different. Always inspect data content first to visualize what can be expected. Make sure to test your code and demonstrate validity of each solution!

Here the number of correct queries and their originality count towards the grade.

Problem #1 (50 points max) – not so easy

Given a QDEL table from the (old i.e. original) QSALE DB provide as many alternatives in SQL to select every fifth record.

Problem #2 (50 points max) – moderate

Provide as many different ways using SQL (but no stored procedures or triggers) to accomplish the following task: compare the tables QITEM and NEWITEM to assess if they are now exactly the same.

 

drop table qemp;drop table qdel;drop table qsale;drop table qdept;drop table qspl;drop table qitem;create table qitem(itemname varchar2(30) constraint qitem_itemname_pk primary keyconstraint qitem_itemname_nn not null,itemtype varchar2(8) constraint qitem_itemtype_nn not null,itemcolor varchar2(10));create table qspl(splno number(3) constraint qspl_splno_pk primary keyconstraint qspl_splno_nn not null,splname varchar2(30) constraint qspl_splname_nn not null);create table qdept(deptname varchar2(10) constraint qdept_deptname_pk primary keyconstraint qdept_deptname_nn not null,deptfloor number(1) constraint qdept_deptfloor_nn not null,deptphone number(2) constraint qdept_deptphone_nn not null,empno number(2) constraint qdept_empno_nn not null);create table qsale(saleno number(4) constraint qsale_saleno_pk primary keyconstraint qsale_saleno_nn not null,saleqty number(3) constraint qsale_saleqty_nn not null,itemname varchar2(30) constraint qsale_itemname_fk references qitem(itemname)constraint qsale_itemname_nn not null,deptname varchar2(10) constraint qsale_deptname_fk references qdept(deptname)constraint qsale_deptname_nn not null);create table qdel(delno number(3) constraint qdel_delno_pk primary keyconstraint qdel_delno_nn not null,delqty number(3) constraint qdel_delqty_nn not null,itemname varchar2(30)constraint qdel_itemname_fk references qitem(itemname)constraint qdel_itemname_nn not null,deptname varchar2(10)constraint qdel_deptname_fk references qdept(deptname)constraint qdel_deptname_nn not null,splno number(3)constraint qdel_splno_fk references qspl(splno)constraint qdel_splno_nn not null);create table qemp(empno number(2) constraint qemp_empno_pk primary keyconstraint qemp_empno_nn not null,empfname varchar2(10) constraint qemp_empfname_nn not null,empsalary number(7) constraint qemp_empsalary_nn not null,deptname varchar2(10) constraint qemp_deptname_fk references qdept(deptname)constraint qemp_deptname_nn not null,bossno number(2) constraint qemp_bossno_fk references qemp(empno));-- insert data to qitemINSERT INTO qitem VALUES('boots-snakeproof', 'c', 'green');INSERT INTO qitem VALUES('camel saddle', 'r', 'brown');INSERT INTO qitem VALUES('compass', 'n', NULL);

use [master]goIF DB_ID ('QSALE') IS NOT NULLDROP DATABASE [QSALE]GOCREATE DATABASE [QSALE]-- test following sections separatelyuse qsalego-- test (and adapt if necessary) for SQL Serverdrop table qemp;drop table qdel;drop table qsale;drop table qdept;drop table qspl;drop table qitem;create table qitem(itemname varchar(30) constraint qitem_itemname_pk primary keyconstraint qitem_itemname_nn not null,itemtype varchar(8) constraint qitem_itemtype_nn not null,itemcolor varchar(10));create table qspl(splno int constraint qspl_splno_pk primary keyconstraint qspl_splno_nn not null,splname varchar(30) constraint qspl_splname_nn not null);create table qdept(deptname varchar(10) constraint qdept_deptname_pk primary keyconstraint qdept_deptname_nn not null,deptfloor int constraint qdept_deptfloor_nn not null,deptphone int constraint qdept_deptphone_nn not null,empno int constraint qdept_empno_nn not null);create table qsale(saleno int constraint qsale_saleno_pk primary keyconstraint qsale_saleno_nn not null,saleqty int constraint qsale_saleqty_nn not null,itemname varchar(30) constraint qsale_itemname_fk references qitem(itemname)constraint qsale_itemname_nn not null,deptname varchar(10) constraint qsale_deptname_fk references qdept(deptname)constraint qsale_deptname_nn not null);create table qdel(delno int constraint qdel_delno_pk primary keyconstraint qdel_delno_nn not null,delqty int constraint qdel_delqty_nn not null,itemname varchar(30)constraint qdel_itemname_fk references qitem(itemname)constraint qdel_itemname_nn not null,deptname varchar(10)constraint qdel_deptname_fk references qdept(deptname)constraint qdel_deptname_nn not null,splno intconstraint qdel_splno_fk references qspl(splno)constraint qdel_splno_nn not null);create table qemp(empno int constraint qemp_empno_pk primary keyconstraint qemp_empno_nn not null,empfname varchar(10) constraint qemp_empfname_nn not null,empsalary money constraint qemp_empsalary_nn not null,

Attachments:

• 1494467342-QSALE-CreateInsert-TBD-SQLServer.txt