Vzorové příklady SQL

   

   

   ářčáářTabulka: Kniha

   CREATE TABLE kniha                                                                             
   (id INTEGER,                                                                                   
   název VARCHAR(50),                                                                             
   PRIMARY KEY (id))                                                                              

   Tabulka: Autoři

   CREATE TABLE autoři                                                                            
   (id INTEGER,                                                                                   
   jméno VARCHAR(10),                                                                             
   příjmení VARCHAR(20),                                                                          
   titul VARCHAR(7),                                                                              
   prostřední VARCHAR(10),                                                                        
   PRIMARY KEY (id))                                                                              

   Tabulka: Napsané_knihy

   CREATE TABLE napsané_knihy                                                                     
   (publikace_id INTEGER,                                                                         
   autor_id INTEGER,                                                                              
   PRIMARY KEY (publikace_id, autor_id),                                                          
   FOREIGN KEY (publikace_id) REFERENCES kniha (id),                                              
   FOREIGN KEY (autor_id) REFERENCES autoři (id))                                                 

   Tato tabulka zprostředkovává vazbu mezi tabulkami KNIHA a AUTOŘI, typu M:N (jeden autor může
   napsat více knih, jedna kniha může být napsána více autory).

   

   

   Tabulka: Nakladatelství

   CREATE TABLE nakladatelství                                                                    
   (id INTEGER,                                                                                   
   název VARCHAR(20),                                                                             
   adr_ulice VARCHAR(20),                                                                         
   adr_číslo INTEGER,                                                                             
   adr_město VARCHAR(15),                                                                         
   adr_psč NUMBER(5,0),                                                                           
   telefon VARCHAR(15),                                                                           
   email VARCHAR(40),                                                                             
   web_stránka VARCHAR(60),                                                                       
   PRIMARY KEY (id))                                                                              

   

   Tabulka: Výtisk

   CREATE TABLE výtisk                                                                            
   (id INTEGER,                                                                                   
   publikace_id INTEGER,                                                                          
   nakladatel_id INTEGER,                                                                         
   vazba VARCHAR(10) DEFAULT 'pevná',                                                             
   cena NUMBER(8,2) DEFAULT 0,                                                                    
   počet_stran INTEGER,                                                                           
   hmotnost NUMBER(6,1),                                                                          
   vydání INTEGER,                                                                                
   rok_vydání INTEGER,                                                                            
   PRIMARY KEY (id),                                                                              
   FOREIGN KEY (kniha_id) REFERENCES kniha (id),                                                  
   FOREIGN KEY (nakladatel_id) REFERENCES nakladatelství (id))                                    

   

   ářčáářJednoduchý výpis tabulky

   ářčáář

   +--------------------------------------------------------------------------------------------+
   |SELECT id, název                                                                            |
   |FROM kniha                                                                                  |
   |                                                                                            |
   |SELECT *                                                                                    |
   |FROM autoři                                                                                 |
   |                                                                                            |
   |SELECT název, adr_ulice, adr_číslo, email                                                   |
   |FROM nakladatelství                                                                         |
   +--------------------------------------------------------------------------------------------+

   

   ářčáářPříklady

   Názvy všech nakladatelství, která jsou z Brna:

   SELECT název                                                                                   
   FROM nakladatelství                                                                            
   WHERE adr_město = ‘Brno’                                                                       
                                                                                                  
                                                                                                  

   Název a e-mail všech nakladatelství, která mají svou webovskou stránku:

   SELECT název, email                                                                            
   FROM nakladatelství                                                                            
   WHERE web_stránka IS NOT NULL                                                                  

   Příjmení všech autorů, jejichž křestní jména začínají na písmeno 'T', a kteří nemají žádný
   titul:

   SELECT příjmení                                                                                
   FROM autoři                                                                                    
   WHERE jméno LIKE 'T%'                                                                          
   AND titul IS NULL                                                                              

   ářčáářSeznam jmen všech brněnských nakladatelství s jejich kompletními adresami, tříděno dle
   názvů ulic (pakliže sídlí ve stejné ulici, dle jejich čísla):

   SELECT název, adr_ulice, adr_číslo, adr_město, adr_psč                                         
   FROM nakladatelství                                                                            
   WHERE adr_město = ‘Brno’                                                                       
   ORDER BY adr_ulice, adr_číslo                                                                  

   ářčáářNázvy všech knih, jejichž výtisky byly vydány v roce 1998:

   SELECT název                                                                                   
   FROM kniha, výtisk                                                                             
   WHERE kniha.id = výtisk.kniha_id                                                               
   AND rok_vydání = 1998                                                                          

   Kteří autoři napsali knihu "Jak publikovat články"?

   SELECT příjmení, jméno                                                                         
   FROM autoři, kniha, napsané_knihy                                                              
   WHERE kniha.id = napsané_knihy.kniha_id                                                        
   AND napsané_knihy.autor_id = autoři.id                                                         
   AND kniha.název LIKE 'Jak publikovat články'                                                   

   Názvy všech knih, které napsal Alois Jirásek:

   SELECT název                                                                                   
   FROM kniha, autoři, napsané_knihy                                                              
   WHERE kniha.id = napsané_knihy.kniha_id                                                        
   AND autoři.id = napsané_knihy.autor_id                                                         
   AND jméno = 'Alois'                                                                            
   AND příjmení = 'Jirásek'                                                                       

   Názvy všech děl, která vydalo nakladatelství 'Iota':

   SELECT kniha.název                                                                             
   FROM kniha, výtisk, nakladatelství                                                             
   WHERE kniha.id = výtisk.kniha_id                                                               
   AND výtisk.nakladatel_id = nakladatelství.id                                                   
   AND nakladatelství.název = 'Iota'                                                              

   Ve kterých nakladatelstvích byla v roce 1996 vydána díla autora Robina Cooka?

   SELECT nakladatelství.název                                                                    
   FROM nakladatelství, výtisk, kniha, napsané_knihy, autoři                                      
   WHERE nakladatelství.id = výtisk.nakladatel_id                                                 
   AND výtisk.kniha_id = kniha.id                                                                 
   AND kniha.id = napsané_knihy.kniha_id                                                          
   AND napsané_knihy.autor_id = autoři.id                                                         
   AND nakladatelství.rok_vydání = 1996                                                           
   AND autoři.jméno = 'Robin'                                                                     
   AND autoři.příjmení = 'Cook'                                                                   

   ářčáářVypište seznam měst, ve kterých sídlí naše nakladatelství, u každého města uveďte, kolik
   z nakladatelstvích má kontaktní e-mail nebo webovskou stránku:

   SELECT adr_město, COUNT(*)                                                                     
   FROM nakladatelství                                                                            
   WHERE web_stránka IS NOT NULL                                                                  
   OR email IS NOT NULL                                                                           
   GROUP BY adr_město                                                                             

   ářčáářSeznam všech názvů knih a nakladatelství a pro každou dvojici kniha - nakladatelství
   součet cen výtisků daného díla v daném nakladatelství, napsali bychom následující příkaz:

   SELECT kniha.název, nakladatelství.název, SUM(cena)                                            
   FROM kniha, výtisk, nakladatelství                                                             
   WHERE kniha.id = výtisk.kniha_id                                                               
   AND výtisk.nakladatel_id = nakladatelství.id                                                   
   GROUP BY kniha.název, nakladatelství.název                                                     

   Pářčáářouze brněnská nakladatelství a pro všechny knihy, které byly těmito nakladatelstvími
   vydány, zjistěte průměrnou hmotnost:

   SELECT kniha.název, nakladatelství.název, AVG(hmotnost)                                        
   FROM kniha, výtisk, nakladatelství                                                             
   WHERE nakladatelství.id = výtisk.nakladatel_id                                                 
   AND výtisk.kniha_id = kniha.id                                                                 
   AND adr_město = ‘Brno’                                                                         
                                                                                                  
   GROUP BY kniha.název, nakladatelství.název                                                     

   Sářčáářeznam všech knih a nakladatelství a pro každou dvojici kniha - nakladatel součet cen
   výtisků daného díla v daném nakladatelství. Do výpisu zahrneme ale jen ta nakladatelství,
   která vydala alespoň 3 různá výdání dané knihy.

   SELECT kniha.název, nakladatelství.název, SUM(cena)                                            
   FROM kniha, výtisk, nakladatelství                                                             
   WHERE kniha.id = výtisk.kniha_id                                                               
   AND výtisk.nakladatel_id = nakladatelství.id                                                   
   GROUP BY kniha.název, nakladatelství.název                                                     
   HAVING count(*) >=3                                                                            

   ářčáářSeznam všech děl, které napsal Alois Jirásek, nebo Vítězslav Nezval.

   SELECT název                                                                                   
   FROM kniha, napsané_knihy, autoři                                                              
   WHERE kniha.id = napsané_knihy.kniha_id                                                        
   AND napsané_knihy.autor_id = autoři.id                                                         
   AND (                                                                                          
   (jméno = 'Alois' AND příjmení = 'Jirásek')                                                     
   OR                                                                                             
   (jméno = 'Vítězslav' AND příjmení = 'Nezval')                                                  
   )                                                                                              

   Pomocí sjednocení předchozí dotaz lze přepsat následovně:

   SELECT název                                                                                   
   FROM kniha, napsané_knihy, autoři                                                              
   WHERE kniha.id = napsané_knihy.kniha_id                                                        
   AND napsané_knihy.autor_id = autoři.id                                                         
   AND jméno = 'Alois' AND příjmení = 'Jirásek'                                                   
   UNION                                                                                          
   SELECT název                                                                                   
   FROM kniha, napsané_knihy, autoři                                                              
   WHERE kniha.id = napsané_knihy.kniha_id                                                        
   AND napsané_knihy.autor_id = autoři.id                                                         
   AND jméno = 'Vítězslav' AND příjmení = 'Nezval'                                                

   ářčáářNázvy všech knih, které napsal Jan Novák a zároveň Petr Novýářčáář

   +--------------------------------------------------------------------------------------------+
   |SELECT název                                                                                |
   |FROM kniha, napsané_knihy, autoři                                                           |
   |WHERE kniha.id = napsané_knihy.kniha_id                                                     |
   |AND napsané_knihy.autor_id = autoři.id                                                      |
   |AND jméno = 'Jan' AND příjmení = 'Novák'                                                    |
   |INTERSECT                                                                                   |
   |SELECT název                                                                                |
   |FROM kniha, napsané_knihy, autoři                                                           |
   |WHERE kniha.id = napsané_knihy.kniha_id                                                     |
   |AND napsané_knihy.autor_id = autoři.id                                                      |
   |AND jméno = 'Petr' AND příjmení = 'Nový'                                                    |
   +--------------------------------------------------------------------------------------------+

   

   ářčáářSeznam všech knih, které napsal Jan Novák, ale zároveň které nenapsal Petr Novýářčáář

   +--------------------------------------------------------------------------------------------+
   |SELECT název                                                                                |
   |FROM kniha, napsané_knihy, autoři                                                           |
   |WHERE kniha.id = napsané_knihy.kniha_id                                                     |
   |AND napsané_knihy.autor_id = autoři.id                                                      |
   |AND jméno = 'Jan' AND příjmení = 'Novák'                                                    |
   |MINUS                                                                                       |
   |SELECT název                                                                                |
   |FROM kniha, napsané_knihy, autoři                                                           |
   |WHERE kniha.id = napsané_knihy.kniha_id                                                     |
   |AND napsané_knihy.autor_id = autoři.id                                                      |
   |AND jméno = 'Petr' AND příjmení = 'Nový'                                                    |
   +--------------------------------------------------------------------------------------------+

   ářčáářZjistěte název a cenu nejlevnější knihy.

   SELECT název, cena                                                                             
   FROM kniha, výtisk                                                                             
   WHERE kniha.id = výtisk.publikace_id                                                           
   AND cena = (SELECT MIN(cena) FROM výtisk)                                                      

   Vypište jména všech autorů, kteří se podíleli při psaní nejdelší knihy (=s největším počtem
   stran)

   SELECT jméno, příjmení                                                                         
   FROM autoři, napsané_knihy, výtisk                                                             
   WHERE autoři.id = napsané_knihy.autor_id                                                       
   AND napsané_knihy.publikace_id = výtisk.publikace_id                                           
   AND počet_stran = (SELECT MAX(počet_stran) FROM výtisk)                                        

   

   ářčáářNázvy knih vydané v letech 1997 až 2000, mohli bychom takový dotaz pomocí operátoru IN
   zapsat takto:

   SELECT název                                                                                   
   FROM kniha, výtisk                                                                             
   WHERE kniha.id = výtisk.publikace_id                                                           
   AND rok IN (1997, 1998, 1999, 2000)                                                            

   Názvy knih vydaných v týchž letech, jako díla Aloise Jiráska:

   SELECT název                                                                                   
   FROM kniha, autoři, napsané_knihy, výtisk                                                      
   WHERE kniha.id = napsané_knihy.publikace_id                                                    
   AND napsané_knihy.autor_id = autoři.id                                                         
   AND kniha.id = výtisk.publikace_id                                                             
   AND rok IN ( SELECT rok                                                                        
   FROM autoři a, výtisk v, napsané_knihy n                                                       
   WHERE a.id = n.autor_id                                                                        
   AND n.publikace_id = v.publikace_id                                                            
   AND a.jméno LIKE 'Alois'                                                                       
   AND a.příjmení LIKE 'Jirásek'                                                                  
   )                                                                                              

   ářčáářSeznam takových knih od Vítězslava Nezvala, jejichž výtisky nebyly nikdy dražší, než
   kterýkoliv výtisk díla Aloise Jiráska.

   SELECT název                                                                                   
   FROM kniha, výtisk, napsané_knihy, autoři                                                      
   WHERE autoři.id = napsané_knihy.autor_id                                                       
   AND napsané_knihy.publikace_id = kniha.id                                                      
   AND kniha.id = výtisk.publikace_id                                                             
   AND jméno LIKE 'Vítězslav'                                                                     
   AND příjmení LIKE 'Nezval'                                                                     
   AND cena < ALL (SELECT cena                                                                    
   FROM napsané_knihy n, výtisk v, autoři a                                                       
   WHERE v.publikace_id = n.publikace_id                                                          
   AND n.autor_id = a.id                                                                          
   AND a.jméno LIKE 'Alois'                                                                       
   AND a.příjmení LIKE 'Jirásek'                                                                  
   )                                                                                              

   ářčáářChceme tedy dotaz na seznam knih, u kterých nemáme zadaného žádného autora:

   SELECT název                                                                                   
   FROM kniha                                                                                     
   WHERE NOT EXISTS (                                                                             
   SELECT autor_id                                                                                
   FROM napsané_knihy                                                                             
   WHERE kniha.id = napsané_knihy.publikace_id                                                    
   )                                                                                              
                                                                                                  
                                                                                                  

   ářčáářVypište jména autorů, kteří samostatně (bez pomocí jiných) napsali alespoň 5 knih:

   SELECT jméno, příjmení                                                                         
   FROM autoři, napsané_knihy                                                                     
   WHERE napsané_knihy.autor_id = autoři.id                                                       
   AND NOT EXISTS (                                                                               
   SELECT 1                                                                                       
   FROM napsané_knihy x                                                                           
   WHERE x.publikace_id = napsané_knihy.publikace_id                                              
   AND x.autor_id != napsané_knihy.autor_id                                                       
   )                                                                                              
   GROUP BY jméno, příjmení                                                                       
   HAVING COUNT(*) >= 5