Sql Yog

Only available on StudyMode
  • Download(s) : 66
  • Published : January 13, 2013
Open Document
Text Preview
cd C:\wamp\bin\mysql\mysql5.5.8\bin

mysql -u root

(You can use these codes even in command prompt and SQLyog)

/*Creating Database Queries*/

SHOW DATABASES;

CREATE DATABASE Computerfirm;

USE Computerfirm;

CREATE TABLE Product
(
maker VARCHAR(10) NOT NULL,
model VARCHAR(50) NOT NULL,
TYPE VARCHAR(50) NOT NULL,
PRIMARY KEY (model)

);

CREATE TABLE PC
(
CODE INT NOT NULL,
model VARCHAR(50) NOT NULL,
speed SMALLINT NOT NULL,
ram SMALLINT NOT NULL,
hd REAL NOT NULL,
cd VARCHAR(10) NOT NULL,
price FLOAT,
PRIMARY KEY (CODE),
FOREIGN KEY (model) REFERENCES Product(model)
);

);

CREATE TABLE Printer
(
CODE INT NOT NULL,
model VARCHAR(50) NOT NULL,
color CHAR(1) NOT NULL,
TYPE VARCHAR(10) NOT NULL,
price FLOAT,
PRIMARY KEY (CODE),
FOREIGN KEY (model) REFERENCES Product(model)
);

INSERT INTO Product VALUES
('A', '1121', 'pc'),
('B', '1232', 'pc'),
('C', '1233', 'pc'),
('D', '1260', 'pc'),
('A', '1122', 'laptop'),
('B', '1124', 'laptop'),
('D', '1261', 'laptop'),
('E', '1231', 'laptop'),
('F', '1123', 'laptop'),
('A', '1125', 'printer'),
('B', '1262', 'printer'),
('D', '1235', 'printer'),
('A', '1120', 'printer'),
('A', '2222', 'pc'),
('B', '2223', 'pc'),
('B', '2224', 'pc'),
('A', '3333', 'pc'),
('A', '4444', 'pc'),
('same', '7777', 'pc'),
('same', '7778', 'laptop'),
('Press', '9987', 'printer'),
('Press2', '2232', 'printer'),
('Dell', '2252', 'laptop'),
('K1', '6666', 'pc'),
('K1', '6667', 'pc'),
('K1', '6668', 'pc');

INSERT INTO PC VALUES
(1, '1232', 500, 64, 5, '12x', 600.00),
(2, '1121', 750, 128, 14, '40x', 650.00),
(3, '1233', 500, 64, 5, '12x', 600.00),
(4, '1121', 600, 128, 14, '40x', 350.00),
(5, '1121', 600, 128, 8, '40x', 350.00),
(6, '1233', 750, 128, 20, '50x', 950.00),
(7, '1232', 500, 32, 10, '12x', 400.00),
(8, '1232', 450, 64, 8, '24x', 350.00),
(9, '1232', 350, 32, 10, '24x', 350.00),
(10, '1260', 500, 32, 10, '12x', 350.00),
(11, '1260', 900, 128, 40, '40x', 980.00),
(12, '7777', 750, 32, 10, '12x',1600),
(13, '6666', 750, 128, 20,'12x',900),
(14, '6667', 750, 128, 20,'15x',900),
(15, '6668', 750, 128, 20,'18x',900);

INSERT INTO Laptop VALUES
(1, '1122', 900, 128, 6, 12, 1250.50),
(2, '1124', 500, 128, 12, 12, 1350.50),
(3, '1122', 200, 32, 6, 8, 900.75),
(4, '1123', 400, 64, 32, 9, 450.00),
(5, '1231', 300, 64, 8, 12, 500.00),
(6, '1124', 250, 64, 14, 9, 950.70),
(7, '1123', 300, 32, 10, 8, 650.50),
(8, '1261', 1050, 128, 10, 8, 450.25),
(9, '1231', 900, 256, 40, 12, 2000.50),
(10, '1261', 750, 128, 32, 9, 400.30),
(11, '1261', 300, 64, 11, 12, 300.50),
(12,'7778', 450, 64, 20, 13, 500),
(13,'2252', 750, 128, 12,10, 450.00);

INSERT INTO Printer VALUES
(1, '1120', 'Y', 'Matrix', 400.50),
(2, '1262', 'N', 'Jet', 200.00),
(3, '1235', 'Y', 'Matrix', 250.75),
(4, '1125', 'N', 'Laser', 100.25),
(5, '1235', 'Y', 'Jet', 100.25),
(6, '1262', 'N', 'Laser', 100.25),
(7, '1120', 'Y', 'Jet', 250.25),
(8, '1235', 'Y', 'Laser', 400.25),
(9, '1125', 'N', 'Matrix', 500.00),
(10, '1262', 'Y', 'Matrix', 500.00),
(11, '1120', 'N', 'Laser', 350.00),
(12, '2232', 'Y', 'Laser', 900),
(13, '9987', 'Y', 'Matrix', 400);

/*Answers to QUERY Questions*/
1.

/* A Function is NEEDED to ADD the digits. The Function is be made in SQLyog. The code For the Funtion -FunctionSum()is:*/

DELIMITER $$

USE `computerfirm`$$

DROP FUNCTION IF EXISTS `FunctionSum`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `FunctionSum`(model VARCHAR(50)) RETURNS INT(11) DETERMINISTIC
BEGIN
DECLARE a INT DEFAULT 1;

SET @s=0;
WHILE a=450
AND model NOT IN (SELECT model FROM pc WHERE speed < 450);

5.

SELECT DISTINCT model, price FROM Printer HAVING price IN (SELECT MAX(price) FROM Printer);

6.

SELECT DISTINCT maker FROM Pc INNER JOIN Product USING (model);

7. /*A Function is Needed to Count the quantity of printer/pc/laptop. The Function is made in SQLyog. The...
tracking img