Project 1
Task 3
1
SELECT cust_name,city
FROM customer
ORDER BY customer_id;
2
ALTER TABLE Product
RENAME COLUMN Preceding TO prod_;
3
SELECT customer_id from purchased WHERE orders=1;
4)
SELECT name_first,name_last
FROM customer
WHERE name_first LIKE 'A%' OR name_last like 'A%'
order by name_first,name_last asc;
6)
USE Skill_Lync;
CREATE TABLE city (
id int NOT NULL IDENTITY(1, 1),
city_name char(128) NOT NULL,
lat decimal(9,6) NOT NULL,
long decimal(9,6) NOT NULL,
country_id int NOT NULL,
CONSTRAINT city_pk PRIMARY KEY (id)
);
CREATE TABLE country (
id int NOT NULL IDENTITY(1, 1),
country_name char(128) NOT NULL,
country_name_eng char(128) NOT NULL,
country_code char(8) NOT NULL,
CONSTRAINT country_ak_1 UNIQUE (country_name),
CONSTRAINT country_ak_2 UNIQUE (country_name_eng),
CONSTRAINT country_ak_3 UNIQUE (country_code),
CONSTRAINT country_pk PRIMARY KEY (id)
);
ALTER TABLE city ADD CONSTRAINT city_country
FOREIGN KEY (country_id)
REFERENCES country (id);
SELECT * FROM country;
SELECT * FROM city;
INSERT INTO city (city_name, lat, long, country_id) VALUES ('Berlin', 52.520008, 13.404954, 1);
INSERT INTO city (city_name, lat, long, country_id) VALUES ('Belgrade', 44.787197, 20.457273, 2);
INSERT INTO city (city_name, lat, long, country_id) VALUES ('Zagreb', 45.815399, 15.966568, 3);
INSERT INTO city (city_name, lat, long, country_id) VALUES ('New York', 40.73061, -73.935242, 4);
INSERT INTO city (city_name, lat, long, country_id) VALUES ('Los Angeles', 34.052235, -118.243683, 4);
INSERT INTO city (city_name, lat, long, country_id) VALUES ('Warsaw', 52.237049, 21.017532, 5);
INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Deutschland', 'Germany', 'DEU');
INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Srbija', 'Serbia', 'SRB');
INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Hrvatska', 'Croatia', 'HRV');
INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('United Stated of America', 'United Stated of America', 'USA');
INSERT INTO country (country_name, country_name_eng, country_code) VALUES ('Polska', 'Poland', 'POL');