Khayal Vocalists Database: Practicing SQL

Vocalists is a table that here contains details about classical vocalists of the Indian Khayal tradition. I couldn't think of any other way of making SQL learning more interesting.

SELECT * FROM VOCALISTS - This query gives us all the contents of the table 'Vocalists' from the database. The table consists of three columns - Name, Gharana and Artist_ID. For the present, I've made 70 entries into the table. Let's execute the basic SQL Queries on the table.

On the right is the result of the following SQL Query,

SELECT DISTINCT GHARANA 

FROM VOCALISTS

What this query does is selects all the Unique Values of the Gharana Column and prints as output. Out of the 70 entries the database has only 11 unique values for the Gharana Column. And hence, the output. On your right, is the output of the above mentioned query that uses SQL DISTINCT. Uses of DISTINCT are very frequent in any kind of database.

SELECT * FROM VOCALISTS 

WHERE GHARANA = 'GWALIOR'

The table on your right gives all the artists of Gwalior Gharana with their Artist_ID's. If you don't want the Artist_ID and Gharana Column in your output, try this SQL Query,

SELECT NAME FROM VOCALISTS

WHERE GHARANA = 'GWALIOR'

The above mentioned SQL Query will only give you the names of the artists from the Gwalior Gharana and not the Artist_ID and Gharana.

The output shown on the left of your screen is when the folloing query is executed,

SELECT NAME FROM VOCALISTS

WHERE NAME LIKE '%AD%'

This SQL Query finds all the 'Name' entries where 'AD' occurs. If you want to find out the names that start form a particular letter, say, A - then execute the following Query,

SELECT NAME FROM VOCALISTS

WHERE NAME LIKE 'AD%'

Similarly, you can do this for finding out the names that contain, either a specific pattern, or, start or end with a particular character. It is important to mention that the patterns can only be identified with an SQL LIKE Query and not with the following query,

SELECT NAME FROM VOCALISTS

WHERE NAME = 'AD%'

The above query is wrong. Unless and until you use LIKE, you cannot recognize patterns in SQL.

Here's is the output of the following Query - it uses SQL BETWEEN.

SELECT * FROM VOCALISTS

WHERE GHARANA BETWEEN 'A' AND 'H'

The above mentioned SQL Query gives us all the records in which the value of Gharana starts from any letter between A and H including A and H, of course. 

On the right is a the output I got when executed the following query,

SELECT NAME FROM VOCALISTS

WHERE GHARANA = 'KIRANA'

ORDER BY NAME DESC

I wanted only the names all people from Kirana Gharana in Reverse Alphabetical order, and that's what I got. If you want all the other columns to be listed along with the names of the artists, just execute this,

SELECT * FROM VOCALISTS

WHERE GHARANA = 'KIRANA'

ORDER BY NAME DESC

Here's what we get when we execute the following query using SQL IN,

SELECT NAME,ARTIST_ID FROM VOCALISTS

WHERE GHARANA IN ('SHAM CHAURASI','AGRA','DELHI')

ORDER BY ARTIST_ID

If you want the number of entries in a particular column, use SQL COUNT. This query will give you the number of entries in the particular column. 

SELECT COUNT (GHARANA)

FROM VOCALISTS 

Another important command in SQL is the ALTER command. On this table, I am going to add another column named 'Guru'. Let's see how that goes,

ALTER TABLE VOCALISTS

ADD GURU NVARCHAR(50)

After executing the command, I added a column named 'Guru' to the 'Vocalists' table, I executed the following query for displaying the table on right of you, 

SELECT * FROM VOCALISTS

ORDER BY NAME

Now, if you can see that the 'Guru' column has been displayed in all caps, which I don't like for unknown reasons. So, I am going to change 'GURU' to 'Guru'. Let's do this,

ALTER VOCALISTS

RENAME COLUMN GURU TO Guru

But, the magic doesn't seem to be happening because the above mentioned command is for Oracle and not for Microsoft SQL Server 2005. For MS SQL 2005, we'd probably have to use the sp_rename thingy. Let's not get into that now!

Adding values to the new column: Use the following query for adding values to a new column,

UPDATE VOCALISTS

SET GURU = 'Chand Khan'

WHERE NAME = 'Iqbal Ahmed Khan'

We used the UPADTE query because by default SQL allows NULLs and every field in that column called 'Guru' was set to NULL at the time of creation. So, we have to use SQL UPDATE command to update the value of a particular field , and for that we also have to use the WHERE clause to tell the SQL Interpreter what field to update.

Now, using the GROUP BY clause in SQL, we can get the output on our right which, essentially, gives us the names of the artists from Jaipur Gharana in an alphabetical order,

SELECT NAME

FROM VOCALISTS

WHERE GHARANA = 'JAIPUR'

GROUP BY NAME

One alternate for this has already been mentioned earlier in this writing that uses ORDER BY clause,

SELECT NAME 

FROM VOCALISTS

WHERE GHARANA = 'JAIPUR'

ORDER BY NAME

A lot of SQL Queries coming soon!