Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please Help!!! Given a QDEL table from the QSALE DB provide as many alternatives in SQL to select a random 20% of the records. (USING

Please Help!!!

Given a QDEL table from the QSALE DB provide as many alternatives in SQL to select a random 20% of the records. (USING SQL SERVER)

I need 3 more solutions for this problem. All solutions must have different execution plans

create table qitem (itemname varchar(30) constraint qitem_itemname_pk primary key constraint 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 key constraint 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 key constraint 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 key constraint 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 key constraint 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 int constraint qdel_splno_fk references qspl(splno) constraint qdel_splno_nn not null); create table qemp (empno int constraint qemp_empno_pk primary key constraint qemp_empno_nn not null, empfname varchar(10) constraint qemp_empfname_nn not null, empsalary money constraint qemp_empsalary_nn not null, deptname varchar(10) constraint qemp_deptname_fk references qdept(deptname) constraint qemp_deptname_nn not null, bossno int constraint qemp_bossno_fk references qemp(empno)); -- test separately USE QSALE -- insert data to qitem INSERT INTO qitem VALUES ('boots-snakeproof', 'c', 'green'); INSERT INTO qitem VALUES ('camel saddle', 'r', 'brown'); INSERT INTO qitem VALUES ('compass', 'n', NULL); INSERT INTO qitem VALUES ('elephant polo stick', 'r', 'bamboo'); INSERT INTO qitem VALUES ('exploring in 10 easy lessons', 'b', NULL); INSERT INTO qitem VALUES ('geo positioning system', 'n', NULL); INSERT INTO qitem VALUES ('hammock', 'f', 'khaki'); INSERT INTO qitem VALUES ('hat-polar explorer', 'c', 'white'); INSERT INTO qitem VALUES ('how to win foreign friends', 'b', NULL); INSERT INTO qitem VALUES ('map case', 'e', 'brown'); INSERT INTO qitem VALUES ('map measure', 'n', NULL); INSERT INTO qitem VALUES ('pith helmet', 'c', 'khaki'); INSERT INTO qitem VALUES ('pocket knife-avon', 'e', 'brown'); INSERT INTO qitem VALUES ('pocket knife-nile', 'e', 'brown'); INSERT INTO qitem VALUES ('safari chair', 'f', 'khaki'); INSERT INTO qitem VALUES ('safari cooking kit', 'f', NULL); INSERT INTO qitem VALUES ('sextant', 'n', NULL); INSERT INTO qitem VALUES ('stetson', 'c', 'black'); INSERT INTO qitem VALUES ('tent-2 person', 'f', 'khaki'); INSERT INTO qitem VALUES ('tent-8 person', 'f', 'khaki'); -- insert data into qspl INSERT INTO qspl VALUES (101, 'global books and maps'); INSERT INTO qspl VALUES (102, 'nepalese corp.'); INSERT INTO qspl VALUES (103, 'all sports manufacturing'); INSERT INTO qspl VALUES (104, 'sweatshops unlimited'); INSERT INTO qspl VALUES (105, 'all points, inc.'); INSERT INTO qspl VALUES (106, 'sao paulo manufacturing'); -- insert data into qdept INSERT INTO qdept VALUES ('management', 5, 34, 1); INSERT INTO qdept VALUES ('books', 1, 81, 4); INSERT INTO qdept VALUES ('clothes', 2, 24, 4); INSERT INTO qdept VALUES ('equipment', 3, 57, 3); INSERT INTO qdept VALUES ('furniture', 4, 14, 3); INSERT INTO qdept VALUES ('navigation', 1, 41, 3); INSERT INTO qdept VALUES ('recreation', 2, 29, 4); INSERT INTO qdept VALUES ('accounting', 5, 35, 5); INSERT INTO qdept VALUES ('purchasing', 5, 36, 7); INSERT INTO qdept VALUES ('personnel', 5, 37, 9); INSERT INTO qdept VALUES ('marketing', 5, 38, 2); -- insert data into qsale INSERT INTO qsale VALUES (1001, 2, 'boots-snakeproof', 'clothes'); INSERT INTO qsale VALUES (1002, 1, 'pith helmet', 'clothes'); INSERT INTO qsale VALUES (1003, 1, 'sextant', 'navigation'); INSERT INTO qsale VALUES (1004, 3, 'hat-polar explorer', 'clothes'); INSERT INTO qsale VALUES (1005, 5, 'pith helmet', 'equipment'); INSERT INTO qsale VALUES (1006, 1, 'pocket knife-nile', 'clothes'); INSERT INTO qsale VALUES (1007, 1, 'pocket knife-nile', 'recreation'); INSERT INTO qsale VALUES (1008, 1, 'compass', 'navigation'); INSERT INTO qsale VALUES (1009, 1, 'geo positioning system', 'navigation'); INSERT INTO qsale VALUES (1010, 5, 'map measure', 'navigation'); INSERT INTO qsale VALUES (1011, 1, 'geo positioning system', 'books'); INSERT INTO qsale VALUES (1012, 1, 'sextant', 'books'); INSERT INTO qsale VALUES (1013, 3, 'pocket knife-nile', 'books'); INSERT INTO qsale VALUES (1014, 1, 'pocket knife-nile', 'navigation'); INSERT INTO qsale VALUES (1015, 1, 'pocket knife-nile', 'equipment'); INSERT INTO qsale VALUES (1016, 1, 'sextant', 'clothes'); INSERT INTO qsale VALUES (1017, 1, 'sextant', 'equipment'); INSERT INTO qsale VALUES (1018, 1, 'sextant', 'recreation'); INSERT INTO qsale VALUES (1019, 1, 'sextant', 'furniture'); INSERT INTO qsale VALUES (1020, 1, 'pocket knife-nile', 'furniture'); INSERT INTO qsale VALUES (1021, 1, 'exploring in 10 easy lessons', 'books'); INSERT INTO qsale VALUES (1022, 1, 'how to win foreign friends', 'books'); INSERT INTO qsale VALUES (1023, 1, 'compass', 'books'); INSERT INTO qsale VALUES (1024, 1, 'pith helmet', 'books'); INSERT INTO qsale VALUES (1025, 1, 'elephant polo stick', 'recreation'); INSERT INTO qsale VALUES (1026, 1, 'camel saddle', 'recreation'); -- insert data into qdel INSERT INTO qdel VALUES (51, 50, 'pocket knife-nile', 'navigation', 105); INSERT INTO qdel VALUES (52, 10, 'pocket knife-nile', 'books', 105); INSERT INTO qdel VALUES (53, 10, 'pocket knife-nile', 'clothes', 105); INSERT INTO qdel VALUES (54, 10, 'pocket knife-nile', 'equipment', 105); INSERT INTO qdel VALUES (55, 10, 'pocket knife-nile', 'furniture', 105); INSERT INTO qdel VALUES (56, 10, 'pocket knife-nile', 'recreation', 105); INSERT INTO qdel VALUES (57, 50, 'compass', 'navigation', 101); INSERT INTO qdel VALUES (58, 10, 'geo positioning system', 'navigation', 101); INSERT INTO qdel VALUES (59, 10, 'map measure', 'navigation', 101); INSERT INTO qdel VALUES (60, 25, 'map case', 'navigation', 101); INSERT INTO qdel VALUES (61, 2, 'sextant', 'navigation', 101); INSERT INTO qdel VALUES (62, 1, 'sextant', 'equipment', 105); INSERT INTO qdel VALUES (63, 20, 'compass', 'equipment', 103); INSERT INTO qdel VALUES (64, 1, 'geo positioning system', 'books', 103); INSERT INTO qdel VALUES (65, 15, 'map measure', 'navigation', 103); INSERT INTO qdel VALUES (66, 1, 'sextant', 'books', 103); INSERT INTO qdel VALUES (67, 5, 'sextant', 'recreation', 102); INSERT INTO qdel VALUES (68, 3, 'sextant', 'navigation', 104); INSERT INTO qdel VALUES (69, 5, 'boots-snakeproof', 'clothes', 105); INSERT INTO qdel VALUES (70, 15, 'pith helmet', 'clothes', 105); INSERT INTO qdel VALUES (71, 1, 'pith helmet', 'clothes', 101); INSERT INTO qdel VALUES (72, 1, 'pith helmet', 'clothes', 102); INSERT INTO qdel VALUES (73, 1, 'pith helmet', 'clothes', 103); INSERT INTO qdel VALUES (74, 1, 'pith helmet', 'clothes', 104); INSERT INTO qdel VALUES (75, 5, 'pith helmet', 'navigation', 105); INSERT INTO qdel VALUES (76, 5, 'pith helmet', 'books', 105); INSERT INTO qdel VALUES (77, 5, 'pith helmet', 'equipment', 105); INSERT INTO qdel VALUES (78, 5, 'pith helmet', 'furniture', 105); INSERT INTO qdel VALUES (79, 5, 'pith helmet', 'recreation', 105); INSERT INTO qdel VALUES (80, 10, 'pocket knife-nile', 'navigation', 102); INSERT INTO qdel VALUES (81, 1, 'compass', 'navigation', 102); INSERT INTO qdel VALUES (82, 1, 'geo positioning system', 'navigation', 102); INSERT INTO qdel VALUES (83, 10, 'map measure', 'navigation', 102); INSERT INTO qdel VALUES (84, 5, 'map case', 'navigation', 102); INSERT INTO qdel VALUES (85, 5, 'compass', 'books', 102); INSERT INTO qdel VALUES (86, 5, 'pocket knife-avon', 'recreation', 102); INSERT INTO qdel VALUES (87, 5, 'tent-2 person', 'recreation', 102); INSERT INTO qdel VALUES (88, 2, 'tent-8 person', 'recreation', 102); INSERT INTO qdel VALUES (89, 5, 'exploring in 10 easy lessons', 'navigation', 102); INSERT INTO qdel VALUES (90, 5, 'how to win foreign friends', 'navigation', 102); INSERT INTO qdel VALUES (91, 10, 'exploring in 10 easy lessons', 'books', 102); INSERT INTO qdel VALUES (92, 10, 'how to win foreign friends', 'books', 102); INSERT INTO qdel VALUES (93, 2, 'exploring in 10 easy lessons', 'recreation', 102); INSERT INTO qdel VALUES (94, 2, 'how to win foreign friends', 'recreation', 102); INSERT INTO qdel VALUES (95, 5, 'compass', 'equipment', 105); INSERT INTO qdel VALUES (96, 2, 'boots-snakeproof', 'equipment', 105); INSERT INTO qdel VALUES (97, 20, 'pith helmet', 'equipment', 106); INSERT INTO qdel VALUES (98, 20, 'pocket knife-nile', 'equipment', 106); INSERT INTO qdel VALUES (99, 1, 'sextant', 'equipment', 106); INSERT INTO qdel VALUES (100, 3, 'hat-polar explorer', 'clothes', 105); INSERT INTO qdel VALUES (101, 3, 'stetson', 'clothes', 105); -- insert data into qemp INSERT INTO qemp VALUES (1, 'Alice', 75000, 'management', NULL); INSERT INTO qemp VALUES (2, 'Ned', 45000, 'marketing', 1); INSERT INTO qemp VALUES (3, 'Andrew', 25000, 'marketing', 2); INSERT INTO qemp VALUES (4, 'Clare', 22000, 'marketing', 2); INSERT INTO qemp VALUES (5, 'Todd', 38000, 'accounting', 1); INSERT INTO qemp VALUES (6, 'Nancy', 22000, 'accounting', 5); INSERT INTO qemp VALUES (7, 'Brier', 43000, 'purchasing', 1); INSERT INTO qemp VALUES (8, 'Sarah', 56000, 'purchasing', 7); INSERT INTO qemp VALUES (9, 'Sophie', 35000, 'personnel', 1); INSERT INTO qemp VALUES (10, 'Sanjay', 15000, 'navigation', 3); INSERT INTO qemp VALUES (11, 'Rita', 15000, 'books', 4); INSERT INTO qemp VALUES (12, 'Gigi', 16000, 'clothes', 4); INSERT INTO qemp VALUES (13, 'Maggie', 16000, 'clothes', 4); INSERT INTO qemp VALUES (14, 'Paul', 11000, 'equipment', 3); INSERT INTO qemp VALUES (15, 'James', 15000, 'equipment', 3); INSERT INTO qemp VALUES (16, 'Pat', 15000, 'furniture', 3); INSERT INTO qemp VALUES (17, 'Mark', 15000, 'recreation', 3); 

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Databases questions