structured query language

SQL stands for 'Structured Query Language', which will use to interact with database to perform Create, Read, Update, Delete operations on database tables. SQL is also a language, where as SQL server is a database. To work on SQL, a Database software RDBMS is required. RDBMS stands as Relation Data Base Management System. SQL is not case sensitive.

If your using Oracle SQL, you may get the below errors. If your using MySQL, you forget about this errors and move further for commands.

Troubleshooting Oracle

The account is locked

Steps to rectify the error
  • Login as username - 'system' & password - 'manager' or password - 'tiger'
  • SQL> show user;
       User is 'SYSTEM'

       SQL>ALTER user<username> account unblock;

       Ex: ALTER user scott account unlock;
       User altered.

       To close/exit the SQL command prompt usually we will use EXIT command.        SQL> exit;

TNS: protocol adapter error

To troubleshoot the above error, first we need to know the root cause of the issue. The above problem root cause is "Oracle service has not started"

To manually start the Oracle service, follow the below suggestions, 
Settings --> Control Panel --> Administrative Tools --> Services
ctr+r --> services.msc --> right click on Oracle --> properties  --> start service. 

There us an "Oracle Service ORCL" & "start the service"


To login into MySQL use the below command

UNIX: # [mysql dir]/bin/mysql -h hostname -u root -p

Windows: [mysql dir]/bin/mysql -u root -p

Note: [mysql dir] - indicates you where you have installed mysql in your system.

After executing the above command, that will ask you to enter the password of the root user. Once you will enter the password that will takes you into the mysql.

To create database use the below command in the UNIX and Windows will use same command only.

Syntax> create database [database name];

Ex: create database javatbrains;

To see your created database is present in the database list execute the below command.

Ex> show databases;

If your created database name is present in the db list, then you have to execute the command to use your database. To use created database we need to execute use command. This is also be same as in UNIX and Windows.

Syntax> use [database name];

Ex: use javatbrains;

If without executing the use command you can't do any operations in DB. If you have tried to do any operations without selecting the DB, you may get the below exception in MySQL.

ERROR 1046 (3D000): No database selected

If you want to see what all tables are present in the selecting database execute the below commands with respective.

SQL> select * from tab;

Ex> show tables;

This query shows the list of tables.
* - selects all

To see the table description, there is describe or desc commands are available.

Syntax> describe <table name>;

Ex> desc dept;

This query gives the description of the table "dept". The description of the table has column names, constraints, datatypes

NOTE: If your using Oracle, String data type as VARCHAR2, you used MySQL String data type as VARCHAR.

To drop or delete database will use the drop command,

Syntax> drop database <database name>;

Ex: drop database javatbrains;

If your deleting database is not available in database. you will get the below exception in MySQL.

ERROR 1008 (HY000): Can't drop database 'javatbrains'; database doesn't exist 

To delete table from database also we will use the same drop command.

Syntax> drop table <table name>;

Ex: drop table test;

If your trying table doesn't exists in the respective database, you may get the below error.

ERROR 1051 (42S02): Unknown table 'mysql.test'

Following command to see the data in a more orderly fashion.

Syntax> set linesize <number>;

Ex> set linesize 120;

The set linesize command helps in increasing the line size, thus the data is arranged in a orderly fashion.

Syntax> set pagesize <number>;

Ex> set pagesize 20;

The above command "set pagesize 20" increases the page size, thus accommodating more number of rows in a single page.

Ex> select ename, job, sal from emp;

To show all data in a the table, will use the select command. 

Syntax>select * from <table name>;

Ex> select * from dept;

To select specific column values from a table.

Syntax> select column1,column2,.. from <table name>;

Ex> select dept_Id,dept_name from dept;

To select specific rows where the values is matching with 'xyz'.

Syntax> SELECT * from <table name> where <condition>;

Ex: SELECT * from dept where dept_name='xyz';

If you have entered any of the column name as wrong or it's not matching to table column, you will get the below error.

Error Code: 1054. Unknown column 'dept_name ' in 'field list'

To see all rows where two where conditions are matching.

Syntax> SELECT * from <table name> where <condition 1> and <condition 2>;

Ex> SELECT * from dept where dept_name='xyz' and emp_id=121;

Show details where not matching the name and matching id using order by emp_id;

Syntax> SELECT * from <table name> where <condition 1> and <condition 2> order by <field>;

Ex> SELECT * from dept where dept_name!='xyz' and emp_id=121 order by emp_id;

Show all records starting with the name as char 's'

Syntax> SELECT * from <table name> where <column> like '<value>%';

Ex> SELECT * from dept where dept_name like 's%';

Show records starting with the name as char 's' and limit in characters in 1 to 10 

Syntax> SELECT * from <table name> where <column> like '<value>%' limit <Start_index>,<end_index>;

Ex> SELECT * from dept where dept_name like 's%' limit 1,10;

We will also use regular expression to find records. Use 'REGEXP BINARY' to force case-sensitivity. This finds any rec beginning with 'a'

Syntax> SELECT * from <table name> where <column> RLIKE "^<value>";

Ex> SELECT * from dept where rec RLIKE "^a";

To show unique records will use the command called DISTINCT,

Syntax> SELECT DISTINCT <Column Name> from <table name> where <condition>;

Ex> SELECT DISTINCT dept_id, dept_name from dept where dept_name='development';

To show selected records in sorted ascending(ASC) or descending(DESC) order.

Syntax> SELECT <col1>,<col2> from <table name> ORDER BY <Col2> [DESC or ASC];

Ex> SELECT dept_id, dept_name from dept where dept_name ORDER BY DESC;


Popular posts from this blog

Multithreading in java with example

String interview questions and answers

JNDI configuration for Tomcat 9 with Oracle