XII-CS SQL Practical Book Solution

Chapter-12.1

PROBLEM 1

Create customer table

-- (a) Customer Table
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer`(
`CUSTNUMB` INT PRIMARY KEY,
`CUSTNAME` VARCHAR(35),
`ADDRESS` VARCHAR(100),
`BALANCE` DECIMAL(8,2),
`CREDLIM` INT,
`SLSRNUMB` INT
)ENGINE=INNODB;

-- adding data for customer table
INSERT INTO `customer`(`CUSTNUMB`,`CUSTNAME`,`ADDRESS`,`BALANCE`,`CREDLIM`,`SLSRNUMB`) VALUES
(124,'TINA ADAMS','481 Tilak Lane, CP, Delhi',41800.75,50000,3),
(256,'R VENKAT','215 Mylapore, Chennai',100000.75,80000,6),
(311,'PRAKASH DESHMUKH','48 Sea Link Road, Mumbai',20000.10,30000,12),
(315,'K R Rao','914,K.R. Puram, Bangalore',32000.75,30000,6),
(405,'BOB MUKHERJEE','519 Salk Lake city, Kolkata',20100.75,80000,12),
(412,'LUBNA ADAMS','16, Pedar Road, Mumbai',90800.75,100000,3),
(522,'PRABHNOOR SINGH','108 CB, Vasant Kunj, Delhi',49000.50,80000,12),
(567,'BHUVNA BALAJI','808, Bala Nagar, Hyderabad',20100.20,30000,6),
(587,'JABBAR ALI','512, Kokapet, Hyderabad',57000.75,50000,6),
(622,'PRATHAM JAIN','419, Plot 187, Sector-9, Dwarka, Delhi',57500.50,50000,3);

Create Order details table

-- (b) Order Details
DROP TABLE IF EXISTS `orderdetails`;
CREATE TABLE `orderdetails`(
`ORDNUMB` INT,
`PARTNUMB` CHAR(5),
`NUMBORD` INT,
`QUOTPRIC` DECIMAL(10,2)
)ENGINE=INNODB;

INSERT INTO `orderdetails` VALUES
(12489, 'AX12', 11, 14.95),
(12491, 'BT04', 1, 402.99),
(12491, 'BZ66', 1, 311.95),
(12494, 'CB03', 4, 175.00),
(12495, 'CX11', 2, 57.95),
(12498, 'AZ52', 2, 22.95),
(12498, 'BA74', 4, 4.95),
(12500, 'BT04', 1, 402.99),
(12504, 'CZ81', 2, 108.99);

Create orders table

-- Orders Table
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`(
`ORDNUMB` INT PRIMARY KEY,
`CUSTNO` INT,
`ORDDTE` DATE
)ENGINE=INNODB;

INSERT INTO `orders` (`ORDNUMB`,`CUSTNO`, `ORDDTE`) VALUES
(12489, 124, '2018-03-01'),
(12491, 311, '2018-03-10'),
(12494, 315, '2018-03-31'),
(12495, 256, '2018-04-15'),
(12498, 522, '2018-04-16'),
(12500, 124, '2018-04-21'),
(12504, 522, '2018-05-05');

Create Parts table

-- Parts Table
DROP TABLE IF EXISTS `parts`;
CREATE TABLE `parts`(
`PARTNUMB` VARCHAR(6) PRIMARY KEY,
`PARTDESC` VARCHAR(50),
`UNONHAND` INT,
`ITEMCLS` CHAR(2),
`WREHSNM` INT,
`UNITPRCE` DECIMAL(6,2)
)ENGINE=INNODB;

# adding data to the parts table
INSERT INTO `parts` (PARTNUMB, PARTDESC, UNONHAND, ITEMCLS, WREHSNM, UNITPRCE) VALUES
('AX12', 'IRON', 104, 'HW', 3, 17.95),
('AZ52', 'SKATES', 20, 'SG', 2, 24.95),
('BA74', 'BASEBALL', 40, 'SG', 1, 4.95),
('BH22', 'TOASTER', 95, 'HW', 3, 34.95),
('BT04', 'STOVE', 11, 'AP', 2, 402.99),
('BZ66', 'WASHER', 52, 'AP', 3, 311.95),
('CA14', 'SKILLET', 2, 'HW', 3, 19.95),
('CB03', 'BIKE', 44, 'SG', 1, 187.5),
('CX11', 'MIXER', 112, 'HW', 3, 57.95),
('CZ81', 'WEIGHTS', 208, 'SG', 2, 108.99);

Create student marks table

DROP TABLE IF EXISTS `stumarks`;
CREATE TABLE `stumarks`(
`stu_id` INT PRIMARY KEY AUTO_INCREMENT,
`stuname` VARCHAR(15),
`marks_1` DOUBLE,
`marks_2` DOUBLE,
`marks_3` DOUBLE
) AUTO_INCREMENT=11;

-- adding data for all colums.
INSERT INTO `stumarks` (stuname, marks_1,marks_2, marks_3) VALUES
('Mikey Sharma',75.0, 65.0,70.0),
('Nigar Sultana',70.0, 69.0,68.0);
-- adding data excluding the marks_2 column
INSERT INTO `stumarks` (stuname, marks_1, marks_3) VALUES
('Josh Mark',80.0, 60.0),
('Ishpreet Kaur',69.0, 70.0);

Solutions to the Problem

(a) Output

SELECT custnumb, custname, address, balance FROM customer WHERE credlim=50000;

(b) Output

SELECT custnumb, custname, address, balance FROM customer WHERE credlim=50000 ORDER BY custnumb DESC;

(c) Output

SELECT custnumb, custname, address, balance FROM customer WHERE credlim=50000 ORDER BY custname;

(d) Output

SELECT custnumb, custname, address, balance FROM customer WHERE credlim=50000 ORDER BY custnumb, custname;

(e) Output

SELECT custnumb, custname, address, balance FROM customer WHERE credlim=50000 ORDER BY custnumb DESC, custname;

(f) Output

SELECT custnumb, custname, address, balance FROM customer WHERE credlim=30000;

(g) Output

SELECT custnumb, custname, address, balance FROM customer WHERE slsrnumb=3 ORDER BY custnumb ASC;

(h) Output

SELECT custnumb, custname, address, balance FROM customer WHERE custname > 'D';

(i) Output

SELECT custnumb, custname, address, balance FROM customer WHERE custname LIKE 'ADAMS%';

(j) Output

SELECT custnumb, custname, address, balance FROM customer WHERE custname LIKE '%ADAMS';

(k) Output

SELECT custnumb, custname, address, balance FROM customer WHERE custname LIKE '__N%';

(l) Output

SELECT custnumb, custname, address, balance FROM customer WHERE custname LIKE '%A%A%';

(m) Output

SELECT custnumb, custname, address, balance FROM customer WHERE custname LIKE '%A%I%';