PL-SQL
Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate & DBMS_SQL
What is Dynamic SQL? Dynamic SQL is a programming methodology for generating and running...
WHERE Clause in MySQL is a keyword used to specify the exact criteria of data or rows that will be affected by the specified SQL statement. The WHERE clause can be used with SQL statements like INSERT, UPDATE, SELECT, and DELETE to filter records and perform various operations on the data.
We looked at how to query data from a database using the SELECT statement in the previous tutorial. The SELECT statement returned all the results from the queried database table.
They are however, times when we want to restrict the query results to a specified condition. The WHERE clause in SQL comes handy in such situations.
The basic syntax for the WHERE clause when used in a MySQL SELECT WHERE statement is as follows.
SELECT * FROM tableName WHERE condition;
HERE
Let's now look at a practical example.
Suppose we want to get a member's personal details from members table given the membership number 1, we would use the following script to achieve that.
SELECT * FROM `members` WHERE `membership_number` = 1;
Executing the above script in MySQL workbench on the "myflixdb" would produce 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.
The WHERE condition in MySQL when used together with the AND logical operator, is only executed if ALL filter criteria specified are met.
SELECT * FROM `movies` WHERE `category_id` = 2 AND `year_released` = 2008;
Executing the above script in MySQL workbench against the "myflixdb" produces the following results.
movie_id title director year_released category_id 2 Forgetting Sarah Marshal Nicholas Stoller 2008 2
SELECT * FROM `movies` WHERE `category_id` = 1 OR `category_id` = 2;
Executing the above script in MySQL workbench against the "myflixdb" produces the following results.
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
SELECT * FROM `members` WHERE `membership_number` IN (1,2,3);
Executing the above script in MySQL workbench against the "myflixdb" 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.
SELECT * FROM `members` WHERE `membership_number` NOT IN (1,2,3);
Executing the above script in MySQL workbench against the "myflixdb" produces the following results.
membership_number full_names gender date_of_birth physical_address postal_address contct_number 4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL
SELECT * FROM `members` WHERE `gender` = 'Female';
Executing the above script in MySQL workbench against the "myflixdb" 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. 4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL
payment_id membership_number payment_date description amount_paid external_reference_number 1 1 23-07-2012 Movie rental payment 2500 11 3 3 30-07-2012 Movie rental payment 6000 NULL
SELECT * FROM `movies` WHERE `category_id`<> 1;
movie_id title director year_released category_id 2 Forgetting Sarah Marshal Nicholas Stoller 2008 2 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
Brain Teaser
SELECT * FROM `movierentals` WHERE `return_date` < '2012-06-25' AND movie_returned = 0;
reference_number transaction_date return_date membership_number movie_id movie_returned 14 21-06-2012 24-06-2012 2 2 0
What is Dynamic SQL? Dynamic SQL is a programming methodology for generating and running...
Aggregate Functions are all about Performing calculations on multiple rows Of a single column of a...
What is Package in Oracle? PL/SQL package is a logical grouping of a related subprogram...
What is the SQL Group by Clause? The GROUP BY clause is a SQL command that is used to group rows...
What is the DELETE Query? MySQL DELETE command is used to delete rows that are no longer required from...
What is SQLite? SQLite is an open-source, embedded, relational database management system,...