SQL and Command SELECT - Exercises

Content of the lesson:

Structure of Input Table

We will realize several SQL queries for the table of online school newspapers. The name of this table is renome_tarticle and it has the following attributes (not all attributes are listed, only the important ones):

• idtarticle - primary key of the table - it contains an integer value which identifies every row (this value is controlled by the property auto_increment - number greater by one than the last added value is automatically used)
• heading - the main headline of the article
• descr - (description) - a short description of the article used for a short characteristic
• text - text of the article - the whole content
• public - two possible values: 1 - article was published and is visible; other value - article was not published and is not visible
• mysort - this attribute is used for sorting data as you want
• del - (delete) - this attribute is used to determine whether this article was deleted or not. Value 0 means not deleted, other values mean deleted
• viewedcount - number of views of this article
• createdate - date and time when this article was created

For better orientation you can see the following image illustrating the structure of our table.

The table can be created by hand using this image or you can use the following SQL query to create this table:

CREATE TABLE IF NOT EXISTS `renome_tarticle` (
  `idtarticle` int(11) NOT NULL auto_increment,
  `idccathegory` int(11) NOT NULL default '0',
  `idcreporter` int(11) default NULL,
  `heading` varchar(255) collate cp1250_czech_cs default NULL,
  `descr` mediumtext collate cp1250_czech_cs,
  `text` longtext collate cp1250_czech_cs,
  `public` tinyint(4) default '0',
  `tocome` tinyint(4) default '0',
  `mainpage` tinyint(4) default '0',
  `mysort` mediumint(9) default '0',
  `mysortmp` mediumint(9) default '0',
  `del` tinyint(4) default '0',
  `html` tinyint(4) default NULL,
  `viewedcount` mediumint(9) default '0',
  `createdate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`idtarticle`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs AUTO_INCREMENT=1;        
        

To be able to work with this table (to choose data), it is not enough to create the structure of this table but you should fill the table with data. You can download this file: renome-sql-dump.txt for this purpose. The content of this file should be imported into MySQL (you can copy the content and paste it to phpmysqladmin inside the field for SQL query and run it). An illustration of inserted data is available in the following image.

Realizing Single Queries

We will try to create several SQL queries for the table tarticle in this part of lesson. These queries will return (view) data according to our conditions. Realize all queries using the phpmysqladmin and the window to enter SQL queries.

Query 1

Headline and description of all articles from the table tarticle.

SELECT 
    heading, descr  
FROM 
    renome_tarticle

Query 2

Headline and description of all articles from the table tarticle sorted by the creation date.

SELECT 
    heading, 
    descr, 
    createdate 
FROM 
    renome_tarticle 
ORDER BY 
    createdate

Query 3

Headline and creation date of all articles from the table tarticle which were created in 2009.

Help: use the SQL function YEAR (you can read about this function inside the MySQL manual).

SELECT 
    heading,                  
    createdate 
FROM 
    renome_tarticle 
WHERE 
    YEAR(createdate)=2009

Query 4

Headline and creation date of all articles from the table tarticle which were created in the school year 2009.

Help: use the SQL functions YEAR, MONTH (you can read about this functions inside the MySQL manual) and more complex conditions using AND and OR.

SELECT 
    heading,                  
    createdate 
FROM 
    renome_tarticle 
WHERE 
    ((YEAR(createdate)=2009) AND (MONTH(createdate)>8) OR (YEAR(createdate)=2010) AND (MONTH(createdate)<7))

Query 5

Headline and category identifier of all articles from the table tarticle which belong to the category "K zamyšlení".

To be able to write such a query you need one more table which contains information about categories. Without this table you can see only the identifier but you do not know the name of this category.

• idccathegory - primary key of table - integer value which is unique for each row (this value is controlled by the property auto_increment - number greater by one than the last added value is automatically used)
• name - name of category
• mysort - this attribute is used for sorting data as you want
• href - this attribute contains the link to website (optional)
• del - (delete) - this attribute is used to determine whether this category was deleted or not. Value 0 means not deleted, other values mean deleted

You can download the table ccathegory filled with data in SQL format here: renome_ccathegory-sql-dump.txt. You should import the content of this file into MySQL (you can copy the content and paste it to phpmysqladmin inside the field for SQL query and run it). An illustration of inserted data is available in the following image.

You can see that the category "K zamyšlení" is identified with the value 6.

SELECT 
    heading, idccathegory
FROM 
    renome_tarticle 
WHERE 
    idccathegory = 6

Query 6

Headline and category identifier of all articles from the table tarticle which are from categories "K zamyšlení" or "Školní šepoty".

SELECT 
    heading, idccathegory
FROM 
    renome_tarticle 
WHERE 
    (idccathegory = 6 OR idccathegory = 2)

Query 7

All attributes of articles from the table tarticle which are from categories "K zamyšlení" or "Školní šepoty" and are also published.

SELECT 
    *
FROM 
    renome_tarticle 
WHERE 
    (idccathegory = 6 OR idccathegory = 2) AND public = 1

Additional Texts

Questions

1. What is the meaning of the command SELECT in SQL?
2. Characterize possibilities of the command SELECT.
3. What is the meaning of the part WHERE inside the command SELECT?
4. What is the meaning of the part ORDER BY inside the command SELECT?
5. How can you create more difficult conditions inside the command SELECT?