Sql Yog

Topics: Personal computer, SQL, Articles with example SQL code Pages: 10 (1476 words) Published: January 13, 2013
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...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • SQL-an analysis Essay
  • Essay about Sql Server
  • Sql Research Paper
  • Learn Sql Essay
  • Database Sql Essay
  • Some SQL Multiple Choice revision Essay
  • My Sql Architecture Essay
  • SQL Exam Paper

Become a StudyMode Member

Sign Up - It's Free