SQL Queries – Solving with Azure Datastudio

SQL Server is a versatile commonly used RDBMS. Querying with SQL Server is fun, easier, and can able to handle any complex situations easily. In this tutorial, let us see a few important scenarios which we come across in our daily lives and how to solve them by writing efficient queries. Let us use Azure Datastudio for manipulating queries.

Azure Datastudio can be installed easily and it is an open-source tool available for all operating systems.

Step 1: Database Creation 

CREATE DATABASE GeeksforGeeks;
GO

Suppose you have a table having junk values like “#” in the column data and while displaying the data from the table you want to remove the junk values(#) and display only the correct data. Let us see how to handle this scenario.  Execute the below code one by one reading the comment.

Step 2: Sample table creation:

USE GEEKSFORGEEKS

Step 3: Create a table to hold the sample data

CREATE TABLE Extract_data
(
Id INT IDENTITY(1,1),
[Column_data] VARCHAR(100)
)
GO

Step 4: Verify the created table, as there is no data inserted, no rows are seen

select * from
Extract_data

Step 5: Insert the sample data into the newly created table

INSERT INTO Extract_data VALUES
('227;#00.050;#150;#00.043'),
('455;#00.099;#432;00.014#'),
('112;00.055#;323#;#00.015')
GO

Step 6: Check the table data

select * from Extract_data

Step 7: To remove the junk values(#) and display only the correct data, you can use the REPLACE() function. The REPLACE() function in the below query replaces the junk column data ‘#’ with a space.

select REPLACE([Column_data], '#', '')
from Extract_data

NOTE: You can also verify this by checking the number of characters in the Column data using another function DATALENGTH(). 

Step 8: Number of characters in the Column Data with junk characters

select Column_data, datalength([Column_data] ) as TotalCharacters from Extract_data

Step 9: Number of characters in the Displayed Column data after removing the JUNK character(#)

select REPLACE([Column_data], '#', '') as Column_data,
datalength(REPLACE([Column_data], '#', '') ) as TotalCharacters
from Extract_data

NOTE: The above query will remove the JUNK characters only during the display and not removed permanently from the table. If you check the original table data again, the JUNK characters are still there!!

Step 10: If you want to remove the JUNK characters(#) permanently in the table, you can use the query below.

update Extract_data
set Column_data = REPLACE([Column_data], '#', '')

Step 11: Now if you check the Column data in the table, it will be without the JUNK characters.

select * from Extract_data

Now suppose to create tables with the same name in a database. So in an organization, there are possibilities for the “Sales” and “Marketing” departments. Both departments want to share the same table name called “inventory”.  In order to have this scenario, we need to make queries like this

If you create a table using the below syntax, the table will be created in a pre-defined schema “dbo”

CREATE TABLE table1(
ID INT PRIMARY KEY IDENTITY,
Details VARCHAR(100),
Created_date DATETIME NOT NULL
);

You can verify that the table got created under the default pre-defined schema “dbo”

select A.name as Table_Name,B.name as Schema_Name
from sys.tables A
INNER JOIN sys.schemas B
ON A.schema_id = B.schema_id
where A.name = 'table1'
GO

You can notice the newly created table is created under a default schema “dbo”. Schemas can be helpful when we create two tables with the same name in the database. 

Let see how to define a schema first. We will define 2 schemas (sales, and marketing) using the below query

CREATE SCHEMA sales;
GO
CREATE SCHEMA marketing;
GO

Suppose, you need to store inventory details related to the sales team and marketing team in a separate table, you can store them in two separate tables with the same name as below. It will be in this order: [databasename].[schemaname].[tablename]

CREATE TABLE [GEEKSFORGEEKS].[sales].[inventory]( 
ID INT PRIMARY KEY IDENTITY,
Inventory_Items VARCHAR(100),
);

CREATE TABLE [GEEKSFORGEEKS].[marketing].[inventory](
ID INT PRIMARY KEY IDENTITY,
Inventory_Items VARCHAR(100),
);

You can notice that 2 tables got created with the same name but they can be identified using their schema

select A.name as Table_Name,B.name as Schema_Name
from sys.tables A
INNER JOIN sys.schemas B
ON A.schema_id = B.schema_id
where A.name = 'inventory'
GO

NOTE: You can see that we can create 2 tables with the same name using “Schemas”. But to access the 2 tables you need to call them using the schema name in the prefix

select * from inventory

It will throw an invalid object error

Right approach when we are using schemas :

Mention the schema name to access those tables

select * from sales.inventory
select * from marketing.inventory

By following the schema approach, we can keep the same table name across all schemas. So as a practical example, a sales, marketing, or even presales, stock department (in SQL server terms, it is a schema), etc., can have the same table name like ‘inventory’, ‘stock’, ‘sales order’, ‘purchase order, etc.,