{"id":439,"date":"2024-03-28T10:32:44","date_gmt":"2024-03-28T08:32:44","guid":{"rendered":"https:\/\/kirillsats.wordpress.com\/?page_id=439"},"modified":"2024-10-14T07:28:56","modified_gmt":"2024-10-14T07:28:56","slug":"ab-kinoteatr2-sql","status":"publish","type":"page","link":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439","title":{"rendered":"AB Kinoteatr2 SQL"},"content":{"rendered":"\n

SQL, Kinoteatr<\/h2>\n\n\n\n

FilmTypeLiisamine<\/p>\n\n\n\n

CREATE PROCEDURE filmTypeLisamine
@id int,
@type varchar(100),
@kirjeldus TEXT
AS
BEGIN
INSERT INTO filmtype (filmTypeID, filmType, kirjeldus)
VALUES (@id, @type, @kirjeldus);
SELECT * FROM filmType
END


--kontroll
EXEC filmTypeLisamine 4, '10D', 'super film';<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
filmTypeKustutamine<\/p>\n\n\n\n
CREATE PROCEDURE filmTypeKustuta
@kustutamisID int
AS
BEGIN
\tSELECT * FROM filmType;
\tDELETE FROM filmType
\tWHERE filmTypeID = @kustutamisID;
\tSELECT * FROM filmType;
END
--komtroll
EXEC filmTypeKustuta 4;<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Otsing s\u00f5na esimese t\u00e4hega<\/p>\n\n\n\n
CREATE PROCEDURE otsing1taht
@taht CHAR(1)
AS
BEGIN
SELECT filmtype, kirjeldus
FROM filmtype WHERE filmtype LIKE concat(@taht, '%');
END
--KONTROLL
EXEC otsing1taht 2<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
filmTypeUuendamine<\/p>\n\n\n\n
CREATE PROCEDURE filmTypeUendamine
@id int,
@uusfilmType varchar(100)
AS
BEGIN
\tUPDATE filmtype
\tSET filmtype = @uusfilmType
\tWHERE filmTypeID = @id;
\tSELECT * FROM filmtype;
\tEND

--kontroll
EXEC filmTypeUendamine 1, '2D';<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
TabeliKustutamine<\/p>\n\n\n\n
CREATE PROCEDURE tabeliKustutamine
@tableKustutamine varchar (100)
AS
BEGIN
declare @STMT AS varchar(max)
SET @STMT = CONCAT('DROP TABLE ',  @tableKustutamine);
END;
PRINT @STMT;
BEGIN
EXEC(@STMT);
END
--kontroll

CREATE TABLE test(
testID int);
select * from test;
EXEC tabeliKustutamine test;
select * from test;<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Muudatus<\/p>\n\n\n\n
CREATE PROCEDURE muudatus
@tegevus varchar(10),
@tabelinimi varchar(25),
@veerunimi varchar(25),
@tyyp varchar(25) =null
AS
BEGIN
DECLARE @sqltegevus as varchar(max)
set @sqltegevus=case 
when @tegevus='add' then concat('ALTER TABLE ', 
@tabelinimi, ' ADD ', @veerunimi, ' ', @tyyp)
when @tegevus='drop' then concat('ALTER TABLE ', 
@tabelinimi, ' DROP COLUMN ', @veerunimi)
END;
print @sqltegevus;
begin 
EXEC (@sqltegevus);
END
END;

--\u0434\u043e\u0431\u0430\u0432\u043b\u0435\u043d\u0438\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u0430
EXEC muudatus @tegevus='add', @tabelinimi='filmtype', @veerunimi='test', @tyyp='int';
SELECT * FROM filmtype
--\u0443\u0434\u0430\u043b\u0435\u043d\u0438\u0435 \u0441\u0442\u043e\u043b\u0431\u0446\u0430
EXEC muudatus @tegevus='drop', @tabelinimi='filmtype', @veerunimi='test';
SELECT * FROM filmtype<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Kasutatud lingid:<\/p>\n\n\n\n
moodle<\/a><\/p>\n\n\n\n
\n\n\n\n
Kodulehele<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
SQL, Kinoteatr FilmTypeLiisamine filmTypeKustutamine Otsing s\u00f5na esimese t\u00e4hega filmTypeUuendamine TabeliKustutamine Muudatus Kasutatud lingid: moodle Kodulehele<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-439","page","type-page","status-publish","hentry"],"yoast_head":"\nAB Kinoteatr2 SQL - Kirill Sats õpimapp | portfoolio<\/title>\n<meta name=\"description\" content=\"Loomine andmebaas kinoteatr\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439\" \/>\n<meta property=\"og:locale\" content=\"et_EE\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"AB Kinoteatr2 SQL - Kirill Sats õpimapp | portfoolio\" \/>\n<meta property=\"og:description\" content=\"Loomine andmebaas kinoteatr\" \/>\n<meta property=\"og:url\" content=\"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439\" \/>\n<meta property=\"og:site_name\" content=\"Kirill Sats õpimapp | portfoolio\" \/>\n<meta property=\"article:modified_time\" content=\"2024-10-14T07:28:56+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/kirillsats.wordpress.com\/wp-content\/uploads\/2024\/03\/image-31.png?w=431\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutit\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=439\",\"url\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=439\",\"name\":\"AB Kinoteatr2 SQL - Kirill Sats õpimapp | portfoolio\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=439#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=439#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/kirillsats.wordpress.com\\\/wp-content\\\/uploads\\\/2024\\\/03\\\/image-31.png?w=431\",\"datePublished\":\"2024-03-28T08:32:44+00:00\",\"dateModified\":\"2024-10-14T07:28:56+00:00\",\"description\":\"Loomine andmebaas kinoteatr\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=439#breadcrumb\"},\"inLanguage\":\"et\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=439\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"et\",\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=439#primaryimage\",\"url\":\"https:\\\/\\\/kirillsats.wordpress.com\\\/wp-content\\\/uploads\\\/2024\\\/03\\\/image-31.png?w=431\",\"contentUrl\":\"https:\\\/\\\/kirillsats.wordpress.com\\\/wp-content\\\/uploads\\\/2024\\\/03\\\/image-31.png?w=431\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?page_id=439#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"AB Kinoteatr2 SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/#website\",\"url\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/\",\"name\":\"Kirill Sats õpimapp | portfoolio\",\"description\":\"Noorem Tarkvaarendaja eriala\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/kirillsats23.thkit.ee\\\/wp\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"et\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"AB Kinoteatr2 SQL - Kirill Sats õpimapp | portfoolio","description":"Loomine andmebaas kinoteatr","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439","og_locale":"et_EE","og_type":"article","og_title":"AB Kinoteatr2 SQL - Kirill Sats õpimapp | portfoolio","og_description":"Loomine andmebaas kinoteatr","og_url":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439","og_site_name":"Kirill Sats õpimapp | portfoolio","article_modified_time":"2024-10-14T07:28:56+00:00","og_image":[{"url":"https:\/\/kirillsats.wordpress.com\/wp-content\/uploads\/2024\/03\/image-31.png?w=431","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutit"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439","url":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439","name":"AB Kinoteatr2 SQL - Kirill Sats õpimapp | portfoolio","isPartOf":{"@id":"https:\/\/kirillsats23.thkit.ee\/wp\/#website"},"primaryImageOfPage":{"@id":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439#primaryimage"},"image":{"@id":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439#primaryimage"},"thumbnailUrl":"https:\/\/kirillsats.wordpress.com\/wp-content\/uploads\/2024\/03\/image-31.png?w=431","datePublished":"2024-03-28T08:32:44+00:00","dateModified":"2024-10-14T07:28:56+00:00","description":"Loomine andmebaas kinoteatr","breadcrumb":{"@id":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439#breadcrumb"},"inLanguage":"et","potentialAction":[{"@type":"ReadAction","target":["https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439"]}]},{"@type":"ImageObject","inLanguage":"et","@id":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439#primaryimage","url":"https:\/\/kirillsats.wordpress.com\/wp-content\/uploads\/2024\/03\/image-31.png?w=431","contentUrl":"https:\/\/kirillsats.wordpress.com\/wp-content\/uploads\/2024\/03\/image-31.png?w=431"},{"@type":"BreadcrumbList","@id":"https:\/\/kirillsats23.thkit.ee\/wp\/?page_id=439#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/kirillsats23.thkit.ee\/wp\/"},{"@type":"ListItem","position":2,"name":"AB Kinoteatr2 SQL"}]},{"@type":"WebSite","@id":"https:\/\/kirillsats23.thkit.ee\/wp\/#website","url":"https:\/\/kirillsats23.thkit.ee\/wp\/","name":"Kirill Sats õpimapp | portfoolio","description":"Noorem Tarkvaarendaja eriala","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/kirillsats23.thkit.ee\/wp\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"et"}]}},"_links":{"self":[{"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/pages\/439","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=439"}],"version-history":[{"count":4,"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/pages\/439\/revisions"}],"predecessor-version":[{"id":1018,"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=\/wp\/v2\/pages\/439\/revisions\/1018"}],"wp:attachment":[{"href":"https:\/\/kirillsats23.thkit.ee\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=439"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}