SQLite
SQLite Create, Alter, Drop Table with Examples
In this article, we will see how to create tables, modify tables and dropping tables in SQLite3...
SELECT QUERY is used to fetch the data from the MySQL database. Databases store data for later retrieval. The purpose of MySQL Select is to return from the database tables, one or more rows that match a given criteria. Select query can be used in scripting language like PHP, Ruby, or you can execute it via the command prompt.
It is the most frequently used SQL command and has the following general syntax
SELECT [DISTINCT|ALL ] { * | [fieldExpression [AS newName]} FROM tableName [alias] [WHERE condition][GROUP BY fieldName(s)] [HAVING condition] ORDER BY fieldName(s)HERE
*
The Star symbol is used to select all the columns in table. An example of a simple SELECT statement looks like the one shown below.
SELECT * FROM `members`;
The above statement selects all the fields from the members table. The semi-colon is a statement terminate. It's not mandatory but is considered a good practice to end your statements like that.
Click to download the myflix DB used for practical examples.
You can learn to import the .sql file into MySQL WorkBench
The Examples are performed on the following two tables
Table 1: members table
membership_ number | full_names | gender | date_of_ birth | physical_ address | postal_ address | contct_ number | |
---|---|---|---|---|---|---|---|
1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | Private Bag | 0759 253 542 | This email address is being protected from spambots. You need JavaScript enabled to view it. |
2 | Janet Smith Jones | Female | 23-06-1980 | Melrose 123 | NULL | NULL | This email address is being protected from spambots. You need JavaScript enabled to view it. |
3 | Robert Phil | Male | 12-07-1989 | 3rd Street 34 | NULL | 12345 | This email address is being protected from spambots. You need JavaScript enabled to view it. |
4 | Gloria Williams | Female | 14-02-1984 | 2nd Street 23 | NULL | NULL | NULL |
Table 2: movies table
movie_id | title | director | year_released | category_id |
---|---|---|---|---|
1 | Pirates of the Caribean 4 | Rob Marshall | 2011 | 1 |
2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 |
3 | X-Men | NULL | 2008 | NULL |
4 | Code Name Black | Edgar Jimz | 2010 | NULL |
5 | Daddy's Little Girls | NULL | 2007 | 8 |
6 | Angels and Demons | NULL | 2007 | 6 |
7 | Davinci Code | NULL | 2007 | 6 |
9 | Honey mooners | John Schultz | 2005 | 8 |
16 | 67% Guilty | NULL | 2012 | NULL |
Getting members listing
Let's suppose that we want to get a list of all the registered library members from our database, we would use the script shown below to do that.
SELECT * FROM `members`;
Executing the above script in MySQL workbench produces the following results.
membership_ number | full_names | gender | date_of_ birth | physical_ address | postal_ address | contct_ number | |
---|---|---|---|---|---|---|---|
1 | Janet Jones | Female | 21-07-1980 | First Street Plot No 4 | Private Bag | 0759 253 542 | This email address is being protected from spambots. You need JavaScript enabled to view it. |
2 | Janet Smith Jones | Female | 23-06-1980 | Melrose 123 | NULL | NULL | This email address is being protected from spambots. You need JavaScript enabled to view it. |
3 | Robert Phil | Male | 12-07-1989 | 3rd Street 34 | NULL | 12345 | This email address is being protected from spambots. You need JavaScript enabled to view it. |
4 | Gloria Williams | Female | 14-02-1984 | 2nd Street 23 | NULL | NULL | NULL |
Our above query has returned all the rows and columns from the members table.
Let's say we are only interested in getting only the full_names, gender, physical_address and email fields only. The following script would help us to achieve this.
SELECT `full_names`,`gender`,`physical_address`, `email` FROM `members`;
Executing the above script in MySQL workbench produces the following results.
full_names | gender | physical_address | |
---|---|---|---|
Janet Jones | Female | First Street Plot No 4 | This email address is being protected from spambots. You need JavaScript enabled to view it. |
Janet Smith Jones | Female | Melrose 123 | This email address is being protected from spambots. You need JavaScript enabled to view it. |
Robert Phil | Male | 3rd Street 34 | This email address is being protected from spambots. You need JavaScript enabled to view it. |
Gloria Williams | Female | 2nd Street 23 | NULL |
Getting movies listing
Remember in our above discussion that we mention expressions been used in SELECT statements. Let's say we want to get a list of movie from our database. We want to have the movie title and the name of the movie director in one field. The name of the movie director should be in brackets. We also want to get the year that the movie was released. The following script helps us do that.
SELECT Concat(`title`, ' (', `director`, ')') , `year_released` FROM `movies`;
HERE
String portions are separated using commas in the Concat () function.
Executing the above script in MySQL workbench produces the following result set.
Concat(`title`, ' (', `director`, ')') | year_released |
---|---|
Pirates of the Caribean 4 ( Rob Marshall) | 2011 |
Forgetting Sarah Marshal (Nicholas Stoller) | 2008 |
NULL | 2008 |
Code Name Black (Edgar Jimz) | 2010 |
NULL | 2007 |
NULL | 2007 |
NULL | 2007 |
Honey mooners (John Schultz) | 2005 |
NULL | 2012 |
Alias field names
The above example returned the Concatenation code as the field name for our results. Suppose we want to use a more descriptive field name in our result set. We would use the column alias name to achieve that. The following is the basic syntax for the column alias name
SELECT `column_name|value|expression` [AS] `alias_name`;
HERE
The above query with a more meaningful column name
SELECT Concat(`title`, ' (', `director`, ')') AS 'Concat', `year_released` FROM `movies`;
We get the following result
Concat | year_released |
---|---|
Pirates of the Caribean 4 ( Rob Marshall) | 2011 |
Forgetting Sarah Marshal (Nicholas Stoller) | 2008 |
NULL | 2008 |
Code Name Black (Edgar Jimz) | 2010 |
NULL | 2007 |
NULL | 2007 |
NULL | 2007 |
Honey mooners (John Schultz) | 2005 |
NULL | 2012 |
Getting members listing showing the year of birth
Suppose we want to get a list of all the members showing the membership number, full names and year of birth, we can use the LEFT string function to extract the year of birth from the date of birth field. The script shown below helps us to do that.
SELECT `membership_number`,`full_names`,LEFT(`date_of_birth`,4) AS `year_of_birth` FROM members;
HERE
Executing the above query in MySQL workbench against the myflixdb gives us the results shown below.
membership_number | full_names | year_of_birth |
---|---|---|
1 | Janet Jones | 1980 |
2 | Janet Smith Jones | 1980 |
3 | Robert Phil | 1989 |
4 | Gloria Williams | 1984 |
We are now going to use MySQL workbench to generate the script that will display all the field names from our categories table.
1. Right Click on the Categories Table. Click on "Select Rows - Limit 1000"
2. MySQL workbench will automatically create a SQL query and paste in the editor.
3. Query Results will be show
Notice that we didn't write the SELECT statement ourselves. MySQL workbench generated it for us.
Now, you might be thinking why learn the SQL SELECT command to query data from the database when you can simply use a tool such as MySQL workbench's to get the same results without knowledge of the SQL language. Of course that is possible, but learning how to use the SELECT command gives you more flexibility and control over your SQL SELECT statements.
MySQL workbench falls in the category of "Query by Example" QBE tools. It's intended to help generate SQL statements faster to increase the user productivity.
Learning the SQL SELECT command can enable you to create complex queries that cannot be easily generated using Query by Example utilities such as MySQL workbench.
To improve productivity you can generate the code using MySQL workbench then customize it to meet your requirements. This can only happen if you understand how the SQL statements work!
In this article, we will see how to create tables, modify tables and dropping tables in SQLite3...
What is Dynamic SQL? Dynamic SQL is a programming methodology for generating and running...
In this tutorial, we will discuss the key difference between SQL and MySQL. Before discussing SQL...
SQLite offers a lot of different installation packages, depending on your operating systems. It...
In this tutorial, we are going to learn how to use SQL in PL/SQL. SQL is the actual component that...
SQL Query Builder tools are used to easily create SQL queries that help you to get fast and...