Illia Blahun portfoolio

|ru_RU
Illia Blahun portfoolio › SQL keel. Harjutus. Auto tabeli loomine.

SQL keel. Harjutus. Auto tabeli loomine.

1.·         Loo autode tabel, kus igaühe kohta on kirjas mark, registrinumber ja tootmisaasta ning registripiirkond. Mõtle, mis väli vajab primaar võti 

CREATE TABLE Autod (
    id INT PRIMARY KEY identity(1,1),
    Mark VARCHAR(50),
    RegNr VARCHAR(20),
    Aasta INT,
    RegPiirk INT
);

INSERT INTO Autod (Mark, RegNr, Aasta, RegPiirk)
VALUES 
('Audi', '123 ABC', 2000, 1),
('Ford', '777 AAA', 1988, 2),
('Ford', 'FIN 772', 2002, 1),
('Nissan', '111 CCC', 2006, 1),
('Toyota', '128 HGF', 2003, 1),
('VAZ', '544 CCH', 1960, 2);

2. Genereeri veel 20  autod mockaroo abil

insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('F350', '1526250136', 1993, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Pajero', '4855137154', 1995, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Envoy', '3932757408', 2005, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('760', '8865621338', 2006, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('FX', '1670506630', 2009, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('M', '9187982714', 2002, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Avalon', '3981010124', 2000, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Express 1500', '5846346995', 2011, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('DTS', '7879878823', 2006, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Ram 1500 Club', '7826300371', 1999, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('914', '4233435976', 1970, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Century', '5830168553', 2005, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('5000CS', '4545109233', 1987, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Corolla', '9586970213', 2008, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Cherokee', '7164911606', 1999, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Pathfinder', '4045854665', 1994, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('CLS-Class', '7463716929', 2008, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('MX-6', '6428762986', 1994, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('DeVille', '8756271824', 2000, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Caliber', '5687327118', 2010, 2);

3. Järjesta autod tootmisaasta järgi kahanevasse järjekorda

SELECT * FROM Autod
ORDER BY Aasta DESC;

4. Väljasta kõik erinevad margid (kasuta DISTINCT)

SELECT DISTINCT Mark
FROM Autod

5. Väljasta enne 1993. aastat toodetud autode registrinumbrid

SELECT RegNr
FROM Autod
WHERE Aasta < 1993;

6. Väljasta enne 1993. aastat toodetud autode registrinumbrid tähestiku järjekorras

SELECT RegNr
FROM Autod
WHERE Aasta < 1993
ORDER BY RegNr ASC;

7. Väljasta autode kõige varasem väljalaskeaasta (MIN)

SELECT MIN(Aasta) AS VanimAasta
FROM Autod;

8. Muuda registrinumbrit autol, mille id on 3 (uus number 333 KKK)

UPDATE Autod
SET RegNr = '333 KKK'
WHERE id = 3;

9. Kustuta auto id-ga 4

DELETE FROM Autod
WHERE id = 4;

10. Lisa uus masin nimekirja. Vaata tabeli sisu.

INSERT INTO Autod (Mark, RegNr, Aasta, RegPiirk)
VALUES ('Nissan', '555 NNN', 2007, 2);

11. Antud tabeli põhjal koosta ise veel kolm SELECT päringu ja kommenteeri neid ja näita tulemust (print screen)

11.1 Autod, mis on toodetud enne 1993. aastat, järjestatud registreerimisnumbri järgi

SELECT RegNr
FROM Autod
WHERE Aasta < 1993
ORDER BY RegNr ASC;

11.2 Autod, mis on toodetud enne 1993. aastat, mille registreerimisnumber algab ‘1’ või ‘7’, ilma kordusteta

SELECT DISTINCT RegNr
FROM Autod
WHERE Aasta < 1993
AND (RegNr LIKE '1%' OR RegNr LIKE '7%')
ORDER BY RegNr ASC;

11.3 Autod, mille registreerimisnumber algab ‘1’ või ‘7’, toodetud enne 1993. aastat, järjestatud registreerimisnumbri järgi

SELECT RegNr
FROM Autod
WHERE Aasta < 1993
AND (RegNr LIKE '1%' OR RegNr LIKE '7%')
ORDER BY RegNr ASC;

XAMPP

1.

CREATE TABLE Autod (
    id INT PRIMARY KEY AUTO_INCREMENT,
    Mark VARCHAR(50),
    RegNr VARCHAR(20),
    Aasta INT,
    RegPiirk INT
);

INSERT INTO Autod (Mark, RegNr, Aasta, RegPiirk)
VALUES 
('Audi', '123 ABC', 2000, 1),
('Ford', '777 AAA', 1988, 2),
('Ford', 'FIN 772', 2002, 1),
('Nissan', '111 CCC', 2006, 1),
('Toyota', '128 HGF', 2003, 1),
('VAZ', '544 CCH', 1960, 2);

2.

insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('F350', '1526250136', 1993, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Pajero', '4855137154', 1995, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Envoy', '3932757408', 2005, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('760', '8865621338', 2006, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('FX', '1670506630', 2009, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('M', '9187982714', 2002, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Avalon', '3981010124', 2000, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Express 1500', '5846346995', 2011, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('DTS', '7879878823', 2006, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Ram 1500 Club', '7826300371', 1999, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('914', '4233435976', 1970, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Century', '5830168553', 2005, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('5000CS', '4545109233', 1987, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Corolla', '9586970213', 2008, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Cherokee', '7164911606', 1999, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Pathfinder', '4045854665', 1994, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('CLS-Class', '7463716929', 2008, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('MX-6', '6428762986', 1994, 2);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('DeVille', '8756271824', 2000, 1);
insert into Autod (Mark, RegNr, Aasta, RegPiirk) values ('Caliber', '5687327118', 2010, 2);

3.

SELECT * FROM Autod
ORDER BY Aasta DESC;

4.

SELECT DISTINCT Mark
FROM Autod

5.

SELECT RegNr
FROM Autod
WHERE Aasta < 1993;

6. 

SELECT RegNr
FROM Autod
WHERE Aasta < 1993
ORDER BY RegNr ASC;

7.

SELECT ID, MIN(Aasta) AS VanimAasta
FROM Autod;

8.

UPDATE Autod
SET RegNr = '333 KKK'
WHERE id = 3;

9.

DELETE FROM Autod
WHERE id = 4;

10. 

INSERT INTO Autod (Mark, RegNr, Aasta, RegPiirk)
VALUES ('Nissan', '555 NNN', 2007, 2);

11. Antud tabeli põhjal koosta ise veel kolm SELECT päringu ja kommenteeri neid ja näita tulemust (print screen)

11.1

SELECT RegNr
FROM Autod
WHERE Aasta < 1993
ORDER BY RegNr ASC;

11.2

SELECT DISTINCT RegNr
FROM Autod
WHERE Aasta < 1993
AND (RegNr LIKE '1%' OR RegNr LIKE '7%')
ORDER BY RegNr ASC;

11.3

SELECT RegNr
FROM Autod
WHERE Aasta < 1993
AND (RegNr LIKE '1%' OR RegNr LIKE '7%')
ORDER BY RegNr ASC;

Koosta kolm protseduuri, mis sisaldavad vähemalt 1parameeter ja see parameeter sõltub SELECT lause tulemusest.

XAMPP

  1. Auto tootmisaasta saamise kord poolt id
DELIMITER //
CREATE PROCEDURE AutoAasta(IN auto_id INT)
BEGIN
    SELECT Aasta 
    FROM Autod
    WHERE ID = auto_id;
END //
DELIMITER ;

2. Näidatud hinnast odavamate autode väljavõtmise kord

DELIMITER //
CREATE PROCEDURE OdavadAutod(IN max_hind DECIMAL(10,2))
BEGIN
    SELECT Mark, Mudel, Hind
    FROM Autod
    WHERE Hind <= max_hind;
END //
DELIMITER ;

3. Kindla aasta autode loendamise kord

DELIMITER //
CREATE PROCEDURE AutodeArv(IN aasta INT)
BEGIN
    SELECT COUNT(*) AS AutoKogus
    FROM Autod
    WHERE Aasta = aasta;
END //
DELIMITER ;

SQL

1.

CREATE PROCEDURE GetAutoAasta
    @auto_id INT
AS
BEGIN
    SELECT Aasta
    FROM Autod
    WHERE ID = @auto_id;
END;

2.

CREATE PROCEDURE GetOdavadAutod
    @max_hind DECIMAL(10,2)
AS
BEGIN
    SELECT Mark, Mudel, Hind
    FROM Autod
    WHERE Hind <= @max_hind
    ORDER BY Hind ASC;
END;

3.

CREATE PROCEDURE GetAutodeArv
    @aasta INT
AS
BEGIN
    SELECT COUNT(*) AS AutoKogus
    FROM Autod
    WHERE Aasta = @aasta;
END;