Listing the source code file SQLScript_MySQL.5.0

drop table Author;

create table Author(
  IdAuthor INT NOT NULL PRIMARY KEY,
  First_Name varchar(50),
  Last_Name varchar(50),
  Notice varchar(40));

drop table CdTitul;

create table CdTitul(
  IDCDTitul INT NOT NULL PRIMARY KEY,
  Titul varchar(50),
  IDKATEG_ZANER INT,
  IDZaner INT,
  IdVydavatel INT,
  IdCDNosic INT,
  IDCDFormat INT,
  Create_Date DATE,
  IdAuthor1 INT,
  IdAuthor2 INT,
  IdAuthor3 INT,
  IdAuthor4 INT);

drop table Songs;

create table Songs(
  IdSongs INT NOT NULL PRIMARY KEY,
  Name varchar(50),
  IDCDTitul INT,
  IdOrder INT,
  Length INT);

drop table Zaner;

create table Zaner(
  IdZaner INT NOT NULL PRIMARY KEY,
  Name varchar(30),
  IDKateg_Zaner INT);

drop table CDNosic;

create table CDNosic(
  IdCDNosic INT NOT NULL PRIMARY KEY,
  Name varchar(30));

drop table CDFormat;

create table CDFormat(
  IDCDFormat INT NOT NULL PRIMARY KEY,
  Name varchar(30));

drop table Vydavatel;

create table Vydavatel(
  IdVydavatel INT NOT NULL PRIMARY KEY,
  Vydavatel varchar(40));

drop table Kateg_Zaner;

create table Kateg_Zaner(
  IDKateg_Zaner INT NOT NULL PRIMARY KEY,
  Name varchar(40));

DELIMITER $$

CREATE DEFINER = 'root'@'localhost'
PROCEDURE Get_Sequence(
        in SeqName varchar(255),
        out NextVal int
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin

declare rowcount int;
declare v_flag char(1);

select Count(*) into rowcount from All_Sequence where seq_name=SeqName;
if rowcount=0 then
  insert into All_Sequence values(SeqName,1,1,1,'A');
  set NextVal = 1;
else
  set v_flag='N';
  while (v_flag='N') do
    select flag into v_flag from All_Sequence where seq_name=SeqName;
  end while;

  update All_Sequence set flag='N' where seq_name=SeqName;
  select currval+INCREMENT into NextVal from All_Sequence where seq_name=SeqName;
  update All_Sequence set flag='A',currval=NextVal where seq_name=SeqName;
end if;
end$$

DELIMITER ;

insert into Author(IdAuthor, First_Name, Last_Name, Notice)
values (11, 'Paul', 'McCartney', '');
insert into Author(IdAuthor, First_Name, Last_Name, Notice)
values (9, 'Michael', 'Corgonik', '');
insert into Author(IdAuthor, First_Name, Last_Name, Notice)
values (6, 'Michael', 'Flatley', '');
insert into Author(IdAuthor, First_Name, Last_Name, Notice)
values (5, 'skupina', 'Elán', '');
insert into Author(IdAuthor, First_Name, Last_Name, Notice)
values (4, 'Vašo', 'Patejdl', '');
insert into Author(IdAuthor, First_Name, Last_Name, Notice)
values (3, 'Pa¾o', 'Habera', '');
insert into Author(IdAuthor, First_Name, Last_Name, Notice)
values (1, 'skupina', 'ABBA', '');
insert into CdTitul(IDCDTitul, Titul, IDKATEG_ZANER, IDZaner, IdVydavatel, IdCDNosic, IDCDFormat, Create_Date, IdAuthor1, IdAuthor2, IdAuthor3, IdAuthor4)
values (11, 'Yesterday', null, null, 1, 1, 2, '2011-1-11', null, null, null, null);
insert into CdTitul(IDCDTitul, Titul, IDKATEG_ZANER, IDZaner, IdVydavatel, IdCDNosic, IDCDFormat, Create_Date, IdAuthor1, IdAuthor2, IdAuthor3, IdAuthor4)
values (10, 'Mopedova Remix', null, null, null, 1, 2, '2011-1-3', null, null, null, null);
insert into CdTitul(IDCDTitul, Titul, IDKATEG_ZANER, IDZaner, IdVydavatel, IdCDNosic, IDCDFormat, Create_Date, IdAuthor1, IdAuthor2, IdAuthor3, IdAuthor4)
values (9, 'Lord of The Dance', null, null, 1, 2, 1, '2011-1-20', null, null, null, null);
insert into CdTitul(IDCDTitul, Titul, IDKATEG_ZANER, IDZaner, IdVydavatel, IdCDNosic, IDCDFormat, Create_Date, IdAuthor1, IdAuthor2, IdAuthor3, IdAuthor4)
values (8, 'Rabaka', null, null, 4, 1, 2, '2011-1-15', null, null, null, null);
insert into CdTitul(IDCDTitul, Titul, IDKATEG_ZANER, IDZaner, IdVydavatel, IdCDNosic, IDCDFormat, Create_Date, IdAuthor1, IdAuthor2, IdAuthor3, IdAuthor4)
values (7, 'S tebou stale, s tebou', null, null, 1, 1, 2, '2011-1-11', null, null, null, null);
insert into CdTitul(IDCDTitul, Titul, IDKATEG_ZANER, IDZaner, IdVydavatel, IdCDNosic, IDCDFormat, Create_Date, IdAuthor1, IdAuthor2, IdAuthor3, IdAuthor4)
values (6, 'Reklama na ticho', null, null, null, 1, 2, '2011-1-21', null, null, null, null);
insert into CdTitul(IDCDTitul, Titul, IDKATEG_ZANER, IDZaner, IdVydavatel, IdCDNosic, IDCDFormat, Create_Date, IdAuthor1, IdAuthor2, IdAuthor3, IdAuthor4)
values (5, 'Mopedova', null, null, null, 1, 2, '2011-1-3', null, null, null, null);
insert into Songs(IdSongs, Name, IDCDTitul, IdOrder, Length)
values (10, 'Angel', 14, 1, 98);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (12, 'Ostatné', 4);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (11, 'Jazykové kurzy', 4);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (10, 'Rozprávky', 3);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (8, 'Veselohra', 2);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (7, 'Thriller', 2);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (6, 'Komédia', 2);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (5, 'Rock', 1);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (4, 'Klasická hudba', 1);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (3, 'Jazz', 1);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (2, 'Muzikal', 1);
insert into Zaner(IdZaner, Name, IDKateg_Zaner)
values (1, 'Pop', 1);
insert into CDNosic(IdCDNosic, Name)
values (3, 'DVD9');
insert into CDNosic(IdCDNosic, Name)
values (2, 'DVD');
insert into CDNosic(IdCDNosic, Name)
values (1, 'CD');
insert into CDFormat(IDCDFormat, Name)
values (4, 'Ostatné');
insert into CDFormat(IDCDFormat, Name)
values (3, 'Mp3');
insert into CDFormat(IDCDFormat, Name)
values (2, 'Audio');
insert into CDFormat(IDCDFormat, Name)
values (1, 'Video');
insert into Vydavatel(IdVydavatel, Vydavatel)
values (3, 'Iné');
insert into Vydavatel(IdVydavatel, Vydavatel)
values (2, 'Supraphon');
insert into Vydavatel(IdVydavatel, Vydavatel)
values (1, 'Bonton');
insert into Kateg_Zaner(IDKateg_Zaner, Name)
values (4, 'Ostatné');
insert into Kateg_Zaner(IDKateg_Zaner, Name)
values (3, 'Hovorené slovo');
insert into Kateg_Zaner(IDKateg_Zaner, Name)
values (2, 'Film');
insert into Kateg_Zaner(IDKateg_Zaner, Name)
values (1, 'Hudba');

Commit;

Return to original page