1.made the table city
1.CREATE TABLE city(
cityID int PRIMARY key AUTO_INCREMENT,
cityname varchar(20) UNIQUE,
population int);
Insert into city (cityname, population)
VALUES('Tallinn', 650000);
select* from City
2. Routin that lets us to put a new post to the table
BEGIN
INSERT INTO city(cityname, population)
VALUES(name,num );
SELECT * from city;
END
SQL query:
CALL cityadd(‘Narva’, 50000);
3.Search all city name by the first letter
BEGIN
SELECT cityname, papulation
FROM city
WHERE cityname like concat(letter, '%');
END
SQL query:
Call City(’T’) find a City by first name
4. Routin which lets us o delete a city
BEGIN
SELECT * FROM city;
DELETE from city
WHERE cityID=deleteID
SELECT * FROM city;
END
SQL query:
Call citydelete (’3’)
5.Population update, lets to change the numerous of inhabitants of a city
BEGIN
SELECT * from city;
UPDATE city SET population=newnum
WHERE cityname=city;
select * from city;
END
No responses yet