Example of IN and NOT IN SQL Queries

SELECT id FROM stats WHERE POSITION IN (’Manager’, ‘Staff’) SELECT ownerid, ‘is in both orders & antiques’ FROM orders, antiques WHERE ownerid = buyerid UNION SELECT buyerid, ‘is in antiques only’ FROM antiques WHERE buyerid NOT IN (SELECT ownerid FROM orders)
Continue reading…

Enjoyed this post? Share it!

 
 

Advantages of using stored procedures

The use of stored programs can lead to a more secure database. Stored programs offer a mechanism to abstract data access routines, which can improve the maintainability of your code as underlying data structures evolve. Stored programs can reduce network traffic, because the program can work on the data from within the server, rather than […]
Continue reading…

Enjoyed this post? Share it!

 
 

CREATE VIEW privilege in MySql

In the MySql security model, the ability to create views requires a greater privilege than normally provided to create tables. Create user and grant privileges: CREATE USER test@localhost IDENTIFIED BY ‘test123’; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON products.* TO test@localhost; Running the following command will error out since the CREATE VIEW privilege is not given to the user. […]
Continue reading…

Enjoyed this post? Share it!

 
 

Installing MySQL on Linux/Unix

The recommended way to install MySQL on a Linux system is via RPM. MySQL AB makes the following RPMs available for download on its web site: MySQL – The MySQL database server, which manages databases and tables, controls user access, and processes SQL queries. MySQL-client – MySQL client programs, which makes it possible to connect […]
Continue reading…

Enjoyed this post? Share it!

 
 

Escape special characters in SQL statements

There are a number of special characters that needs to be escaped , if you want to include them in a character string. Example character escaping rules: The escape character (\) needs to be escaped as (\\). The single quote (‘) needs to be escaped as (\’) or (”) in single-quote quoted strings. The double […]
Continue reading…

Enjoyed this post? Share it!

 
 

Including comments in SQL Statements

To include comments in a SQL statement, we use the “–” symbol, then enter your comment until the end of the line. SELECT ‘Hello world!’ FROM DUAL; — My first SQL statement! INSERT INTO tutorials VALUES (’w3mentor.com’); — Top rated! CREATE TABLE tutorial ( id INTEGER, — primary key title VARCHAR(80) — tutorial title );
Continue reading…

Enjoyed this post? Share it!

 
 

Evaluate expressions with SQL Statements

we can use the “SELECT expression FROM DUAL” statement to return the calculated value of an expression. “DUAL” is a dummy table in the server. Below are a few examples of evaluating expressions: Example code: SELECT ‘Hello world!’ FROM DUAL; Output: Hello world! SELECT (1+2)*3/4 FROM DUAL; Output: 2.2500 SELECT TRUE FROM DUAL; Output: 1 […]
Continue reading…

Enjoyed this post? Share it!

 
 

Date and Time data types in MySql

MySQL supports the following date and time data types: DATE – A date in the range of ‘1000-01-01’ and ‘9999-12-31’. Default DATE format is “YYYY-MM-DD”. DATETIME – A date with the time of day in the range of ‘1000-01-01 00:00:00’ and ‘9999-12-31 23:59:59’. Default DATETIME format is “YYYY-MM-DD HH:MM:SS”. TIMESTAMP – A timestamp. The range […]
Continue reading…

Enjoyed this post? Share it!

 
 

Differences between BINARY and VARBINARY in MySql

Both BINARY and VARBINARY are both binary byte data types. But they have the following major differences: BINARY stores values in fixed lengths. Values are padded with 0x00. VARBINARY stores values in variable lengths. Values are not padded with any bytes. But 1 or 2 extra bytes are added to store the length of the […]
Continue reading…

Enjoyed this post? Share it!

 
 

Differences between CHAR and NCHAR in MySql

Both CHAR and NCHAR are fixed length string data types. But they have the following differences: CHAR’s full name is CHARACTER. NCHAR’s full name is NATIONAL CHARACTER. By default, CHAR uses ASCII character set. So 1 character is always stored as 1 byte. By default, NCHAR uses Unicode character set. NCHAR data are stored in […]
Continue reading…

Enjoyed this post? Share it!

 
 

Types of string data in MySql

MySQL supports the following string data types: CHAR(n) same as CHARACTER(n) – Fixed width and ” ” padded characters strings. Default character set is ASCII. NCHAR(n) same as NATIONAL CHARACTER(n) – Fixed width and ” ” padded character strings with UTF8 character set. VARCHAR(n) same as CHARACTER VARYING(n) – Variable width character strings. Default character […]
Continue reading…

Enjoyed this post? Share it!