Shown a qsale table from the qsale db provide 5 different alternatives in SQL to
ID: 3756906 • Letter: S
Question
Shown a qsale table from the qsale db provide 5 different alternatives in SQL to select every 5th record.
DataBase:
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 key
constraint 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 key
constraint 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 key
constraint 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 key
constraint 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 key
constraint 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 key
constraint 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 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 '||'&'||' 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);
commit;
Explanation / Answer
1.) SELECT * FROM qsale WHERE saleno%5=0; 2.) SELECT * FROM ( SELECT saleno, ROW_NUMBER() OVER (ORDER BY saleno) AS rownum FROM qsale ) AS t WHERE t.rownum % 5 = 0 -- using ROW_NUM function ORDER BY t.saleno; 3.)SELECT * FROM qsale WHERE (saleno,0) IN (SELECT saleno, MOD(ROWNUM,5) -- using MOD function FROM qsale); 4.)SELECT * FROM ( SELECT rownum,saleno FROM qsale) AS temp -- using dynamic views WHERE MOD(temp.ROWNUM,5) = 0; Will update more..
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.