Skip to main content

Important Queries in SQL Part1 #DOTC_Mdb

Important Queries in SQL Part1.

Hi Everyone,

We already Discussed about Oracle SQL and PL/SQL in my previous blogs . Today i will discuss about some important queries which is useful for you . For previous blogs you can able to see it in archive folder.

Let's Start with Important Queries which is useful for you to writing SQL queries . As in my previous blogs i already discussed about Joins , co-related and sub query . So today we will write some important queries which will be useful for you  .

 

How do we Join 3 tables by using Inner Join ?

Explanation: Suppose we have 3 tables student , student_details and student_details1 with one common id in all the three tables.

We can write below query to Join 3 tables by using inner join:

SELECT

a.ID, a.Name,

b.address

FROM

student AS a

INNER JOIN student_details AS b

ON a.ID = b.ID

INNER JOIN student_details1 AS c

ON b.id = c.id;

 

Below is the output of above query in image :

 

 

Here you can see the output by Joining 3 tables by using Inner Join. For reference you can go through my Oracle Join Blogs.

 

Another Query : How can we rename database when it is in use condition .

For renaming database when it is in use condition we can do it in MS _SQL server and in Oracle as Below :

In MS-SQL Server Database by using alter command we can rename a database First we need to go sys or Master database.

Command is as below :

USE MASTER;

GO

ALTER DATABASE DOTC

MODIFY NAME=DOTC1;

GO

 

In Oracle Database follow the below steps :

First connect as sys user in database command is as below

conn sys/oracle as sysdba

Now you need to find the user id and name for database from user$ table

Select user#,name from user$;

 

Here you will see all the database name with user ID start from 0 to n

Suppose you will show id 54 for your database you need to modify or rename then use below command.

update user$ set name=dotc1 where user#=54;

Database name will be updated to dotc1.

After this use commit command

commit;

Shutdown the database and startup , or restart database services.

Note :Don’t try it in Production or Live server just if you need to practice it in your system then use or can be use in test database if any requirement. As its DBA role.

Another Important Query : How  can we insert multiple values in One statement in SQL.

Suppose we have table department with employee_id and name column Below SQL statement is used to insert multiple values in table.

 

Insert into department

(employee_id,name)

Values(100,'Dheeraj'),

(200,'Diksha'),

(300,'Sakshi'),

(400,'Minu');

Below is the output of above query in image :

 

Here you can see 4 record inserted by using one SQL query , we can insert multiple records in same manner.

Another Query How to Find out the Position of T in word DOTC  in MY SQL

 

We use Locate function to find out specific position of letter in the word. Below is the query for this.

Select LOCATE('T',NAME) as position from department  where name='DOTC';

Output will be 3 as position of T in DOTC is at 3rd point.

Below is the output in image for above query.


Please go through all the queries and for reference you can see my SQL blogs for your reference. In next Blog I will come up with more interesting SQL queries which are useful for you.

Thanks.


Comments

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger et...

Basic SQL Commands in Oracle Database #Diksha Oracle Training Centre

  Basic SQL Commands in Oracle Database.   Hello Everyone, In my previous blog I discussed about SQL and Classification of SQL . Today I will discuss about SQL basic commands which widely used in RDBMS. The Topics of SQL command which I am going to cover in this blog are mainly divided into four Categories: ·         DDL:   DDL consists of commands which are used to define and design the database. ·         DML:   DML consists of commands which are mainly used to manipulate the data in the database. ·        DCL: DCL Consists of commands which deal with the user permissions/access and controls in the database. ·       TCL:   TCL Consist of commands which deal with the transaction in the database. If you want to explore theory part please follow my below blog as in today blog I will discuss about how to use the commands. h...

Pseudo Column in Oracle #Diksha Oracle Training Centre

  Pseudo Column in Oracle. Hello Everyone, Today I will discuss about Pseudo column in Oracle. This topic is very important so before going to read this blog, I suggest you to please see my previous blogs which is in archive folder for your reference so that it’s easy for you to understand this topic. Let’s discuss about Pseudo Column in Oracle. Pseudo column: A pseudo-column is dynamic in Oracle as it behaves like a table column but is not stored in the table. Pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudo columns, but you cannot insert, update, or delete their values. A pseudo column is also similar to a function without arguments. In   Simple word we can say Pseudo column is a column that yields a value when selected, but which is not an actual column of the table. An example is RowID or SysDate. It can be use in combination with the DUAL table. Below are the Pseudo Column commonly used in Oracle Dat...