180+ SQL Interview Questions [2023]- Great Learning

0
380
180+ SQL Interview Questions [2023]- Great Learning


Are you an aspiring SQL Developer? A profession in SQL has seen an upward development in 2023, and you may be part of the ever-so-growing neighborhood. So, in case you are able to indulge your self within the pool of data and be ready for the upcoming SQL interview, then you might be on the proper place.

We have compiled a complete record of SQL Interview Questions and Answers that may turn out to be useful on the time of want. Once you’re ready with the questions we talked about in our record, you can be able to get into quite a few SQL-worthy job roles like SQL Developer, Business Analyst, BI Reporting Engineer, Data Scientist, Software Engineer, Database Administrator, Quality Assurance Tester, BI Solution Architect and extra.

This weblog is split into totally different sections, they’re:

Basic SQL Interview Questions
SQL Interview Questions for Experienced
SQL Interview Questions for Developers
SQL Joins Interview Questions
Advanced SQL Interview Questions
SQL Server Interview Questions
PostgreSQL Interview Questions
SQL Practice Questions
Free Resources to study SQL

Great Learning has ready an inventory of the highest 10 SQL interview questions that may show you how to throughout your interview.

  • What is SQL?
  • What is Database?
  • What is DBMS?
  • How to create a desk in SQL?
  • How to delete a desk in SQL?
  • How to alter a desk identify in SQL?
  • How to create a database in SQL?
  • What is take part SQL?
  • What is Normalization in SQL?
  • How to insert a date in SQL?

Basic SQL Interview Questions

All set to kickstart your profession in SQL? Look no additional and begin your skilled profession with these SQL interview questions for freshers. We will begin with the fundamentals and slowly transfer in direction of barely superior inquiries to set the tempo. If you might be an skilled skilled, this part will show you how to brush up in your SQL abilities.

What is SQL?

The acronym SQL stands for Structured Query Language. It is the standard language used for relational database upkeep and a variety of information processing duties. The first SQL database was created in 1970. It is a database language used for operations comparable to database creation, deletion, retrieval, and row modification. It is sometimes pronounced “sequel.” It may also be used to handle structured information, which is made up of variables referred to as entities and relationships between these entities.

What is Database?

A database is a system that helps in gathering, storing and retrieving information. Databases might be advanced, and such databases are developed utilizing design and modelling approaches.

What is DBMS?

DBMS stands for Database Management System which is liable for the creating, updating, and managing of the database. 

What is RDBMS? How is it totally different from DBMS?

RDBMS stands for Relational Database Management System that shops information within the type of a set of tables, and relations might be outlined between the widespread fields of those tables.

How to create a desk in SQL?

The command to create a desk in SQL is very simple:

 CREATE TABLE table_name (
	column1 datatype,
	column2 datatype,
	column3 datatype,
   ....
);

We will begin off by giving the key phrases, CREATE TABLE, after which we’ll give the identify of the desk. After that in braces, we’ll record out all of the columns together with their information varieties.

For instance, if we wish to create a easy worker desk:

CREATE TABLE worker (
	identify varchar(25),
	age int,
	gender varchar(25),
   ....
);

How to delete a desk in SQL?

There are two methods to delete a desk from SQL: DROP and TRUNCATE. The DROP TABLE command is used to fully delete the desk from the database. This is the command:

DROP TABLE table_name;

The above command will fully delete all the info current within the desk together with the desk itself.

But if we wish to delete solely the info current within the desk however not the desk itself, then we’ll use the truncate command:

DROP TABLE table_name ;

How to alter a desk identify in SQL?

This is the command to alter a desk identify in SQL:

ALTER TABLE table_name
RENAME TO new_table_name;

We will begin off by giving the key phrases ALTER TABLE, then we’ll observe it up by giving the unique identify of the desk, after that, we’ll give within the key phrases RENAME TO and at last, we’ll give the brand new desk identify.

For instance, if we wish to change the “employee” desk to “employee_information”, this would be the command:

ALTER TABLE worker
RENAME TO employee_information;

How to delete a row in SQL?

We will probably be utilizing the DELETE question to delete present rows from the desk:

DELETE FROM table_name
WHERE [condition];

We will begin off by giving the key phrases DELETE FROM, then we’ll give the identify of the desk, and after that we’ll give the WHERE clause and provides the situation on the idea of which we’d wish to delete a row.

For instance, from the worker desk, if we wish to delete all of the rows, the place the age of the worker is the same as 25, then this would be the command:

DELETE FROM worker
WHERE [age=25];

How to create a database in SQL?

A database is a repository in SQL, which may comprise a number of tables.

This would be the command to create a database in sql:

CREATE DATABASE database_name.

What is Normalization in SQL?

Normalization is used to decompose a bigger, advanced desk into easy and smaller ones. This helps us in eradicating all of the redundant information.

Generally, in a desk, we can have plenty of redundant data which isn’t required, so it’s higher to divide this advanced desk into a number of smaller tables which comprise solely distinctive data.

First regular kind:

A relation schema is in 1NF, if and provided that:

  • All attributes within the relation are atomic(indivisible worth)
  • And there are not any repeating components or teams of components.

Second regular kind:

A relation is claimed to be in 2NF, if and provided that:

  • It is in 1st Normal Form.
  • No partial dependency exists between non-key attributes and key attributes.

Third Normal kind:

A relation R is claimed to be in 3NF if and provided that:

  • It is in 2NF.
  • No transitive dependency exists between non-key attributes and key attributes by means of one other non-key attribute

What is take part SQL?

Joins are used to mix rows from two or extra tables, based mostly on a associated column between them.

Types of Joins:

INNER JOIN − Returns rows when there’s a match in each tables.

LEFT JOIN − Returns all rows from the left desk, even when there are not any matches in the proper desk.

RIGHT JOIN − Returns all rows from the proper desk, even when there are not any matches within the left desk.

FULL OUTER JOIN − Returns rows when there’s a match in one of many tables.

SELF JOIN − Used to hitch a desk to itself as if the desk had been two tables, quickly renaming no less than one desk within the SQL assertion.

CARTESIAN JOIN (CROSS JOIN) − Returns the Cartesian product of the units of information from the 2 or extra joined tables.

SQL joins

INNER JOIN:

The INNER JOIN creates a brand new consequence desk by combining column values of two tables (table1 and table2) based mostly upon the join-predicate. The question compares every row of table1 with every row of table2 to seek out all pairs of rows which fulfill the join-predicate.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
INNER JOIN table2
ON table1.commonfield = table2.commonfield;

LEFT JOIN:

The LEFT JOIN returns all of the values from the left desk, plus matched values from the proper desk or NULL in case of no matching be a part of predicate.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
LEFT JOIN table2
ON table1.commonfield = table2.commonfield;

RIGHT JOIN:

The RIGHT JOIN returns all of the values from the proper desk, plus matched values from the left desk or NULL in case of no matching be a part of predicate.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
RIGHT JOIN table2
ON table1.commonfield = table2.commonfield;

FULL OUTER JOIN:

The FULL OUTER JOIN combines the outcomes of each left and proper outer joins. The joined desk will comprise all information from each the tables and fill in NULLs for lacking matches on both facet.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
Left JOIN table2
ON table1.commonfield = table2.commonfield;
Union
SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
Right JOIN table2
ON table1.commonfield = table2.commonfield;

SELF JOIN:

The SELF JOIN joins a desk to itself; quickly renaming no less than one desk within the SQL assertion.

SYNTAX:

SELECT a.col1, b.col2,..., a.coln
FROM table1 a, table1 b
WHERE a.commonfield = b.commonfield;

How to insert a date in SQL?

If the RDBMS is MYSQL, that is how we will insert date:

"INSERT INTO tablename (col_name, col_date) VALUES ('DATE: Manual Date', '2020-9-10')";

What is Primary Key in SQL?

Primary Key is a constraint in SQL. So, earlier than understanding what precisely is a major key, let’s perceive what precisely is a constraint in SQL. Constraints are the principles enforced on information columns on a desk. These are used to restrict the kind of information that may go right into a desk. Constraints can both be column degree or desk degree. 

Let’s take a look at the several types of constraints that are current in SQL:

Constraint Description
NOT NULL Ensures {that a} column can not have a NULL worth.
DEFAULT Provides a default worth for a column when none is specified.
UNIQUE Ensures that each one the values in a column are totally different
PRIMARY Uniquely identifies every row/report in a database desk
FOREIGN Uniquely identifies a row/report in any one other database desk
CHECK The CHECK constraint ensures that each one values in a column fulfill sure situations.
INDEX Used to create and retrieve information from the database in a short time.

You can contemplate the Primary Key constraint to be a mixture of UNIQUE and NOT NULL constraint. This signifies that if a column is ready as a major key, then this explicit column can not have any null values current in it and likewise all of the values current on this column should be distinctive.

How do I view tables in SQL?

To view tables in SQL, all it’s worthwhile to do is give this command:

Show tables;

What is PL/SQL?

PL SQL stands for Procedural language constructs for Structured Query Language. PL SQL was launched by Oracle to beat the restrictions of plain sql. So, pl sql provides in procedural language method to the plain vanilla sql.

One factor to be famous over right here is that pl sql is just for oracle databases. If you don’t have an Oracle database, you then cant work with PL SQL. However, if you happen to want to study extra about Oracle, it’s also possible to take up free oracle programs and improve your information.

While, with the assistance of sql, we had been in a position to DDL and DML queries, with the assistance of PL SQL, we can create features, triggers and different procedural constructs.

How can I see all tables in SQL?

Different database administration techniques have totally different queries to see all of the tables.

To see all of the tables in MYSQL, we must use this question:

present tables;

This is how we will see all tables in ORACLE:

SELECT 
    table_name
FROM
    User_tables;

This is how we will extract all tables in SQL Server:

SELECT 
    *
FROM
    Information_schema.tables;

What is ETL in SQL?

ETL stands for Extract, Transform and Load. It is a three-step course of, the place we must begin off by extracting the info from sources. Once we collate the info from totally different sources, what we’ve got is uncooked information. This uncooked information must be remodeled into the tidy format, which is able to come within the second part. Finally, we must load this tidy information into instruments which might assist us to seek out insights.

How to put in SQL?

SQL stands for Structured Query Language and it’s not one thing you may set up. To implement sql queries, you would want a relational database administration system. There are totally different styles of relational database administration techniques comparable to:

Hence, to implement sql queries, we would want to put in any of those Relational Database Management Systems.

What is the replace command in SQL?

The replace command comes underneath the DML(Data Manipulation Langauge) a part of sql and is used to replace the present information within the desk.

UPDATE workers
SET last_name=‘Cohen’
WHERE employee_id=101;

With this replace command, I’m altering the final identify of the worker.

How to rename column identify in SQL Server?

Rename column in SQL: When it involves SQL Server, it’s not doable to rename the column with the assistance of ALTER TABLE command, we must use sp_rename.

What are the sorts of SQL Queries?

We have 4 sorts of SQL Queries:

  • DDL (Data Definition Language): the creation of objects
  • DML (Data Manipulation Language): manipulation of information
  • DCL (Data Control Language): project and elimination of permissions
  • TCL (Transaction Control Language): saving and restoring modifications to a database

Let’s take a look at the totally different instructions underneath DDL:

Command Description
CREATE Create objects within the database
ALTER Alters the construction of the database object
DROP Delete objects from the database
TRUNCATE Remove all information from a desk completely
COMMENT Add feedback to the info dictionary
RENAME Rename an object

Write a Query to show the variety of workers working in every area? 

SELECT area, COUNT(gender) FROM worker GROUP BY area;

What are Nested Triggers?

Triggers might implement DML through the use of INSERT, UPDATE, and DELETE statements. These triggers that comprise DML and discover different triggers for information modification are referred to as Nested Triggers.

Write SQL question to fetch worker names having a wage higher than or equal to 20000 and fewer than or equal 10000.

By utilizing BETWEEN within the the place clause, we will retrieve the Employee Ids of workers with wage >= 20000 and <=10000.

SELECT FullName FROM EmployeeParticulars WHERE EmpId IN (SELECT EmpId FROM EmployeeSalary WHERE Salary BETWEEN 5000 AND 10000)

Given a desk Employee having columns empName and empId, what would be the results of the SQL question under? choose empName from Employee order by 2 asc;

“Order by 2” is legitimate when there are no less than 2 columns utilized in SELECT assertion. Here this question will throw error as a result of just one column is used within the SELECT assertion. 

What is OLTP?

OLTP stands for Online Transaction Processing. And is a category of software program purposes able to supporting transaction-oriented packages. An important attribute of an OLTP system is its capacity to keep up concurrency. 

What is Data Integrity?

Data Integrity is the peace of mind of accuracy and consistency of information over its complete life-cycle, and is a essential facet to the design, implementation and utilization of any system which shops, processes, or retrieves information. It additionally defines integrity constraints to implement enterprise guidelines on the info when it’s entered into an utility or a database.

What is OLAP?

OLAP stands for Online Analytical Processing. And a category of software program packages that are characterised by comparatively low frequency of on-line transactions. Queries are sometimes too advanced and contain a bunch of aggregations. 

Find the Constraint data from the desk?

There are so many instances the place consumer wants to seek out out the particular constraint data of the desk. The following queries are helpful, SELECT * From User_Constraints; SELECT * FROM User_Cons_Columns;

Can you get the record of workers with similar wage? 

Select distinct e.empid,e.empname,e.wage from worker e, worker e1 the place e.wage =e1.wage and e.empid != e1.empid 

What is an alternate for the TOP clause in SQL?

1. ROWCOUNT operate 
2. Set rowcount 3
3. Select * from worker order by empid desc Set rowcount 0 

Will the next assertion offers an error or 0 as output? SELECT AVG (NULL)

Error. Operand information sort NULL is invalid for the Avg operator. 

What is the Cartesian product of the desk?

The output of Cross Join known as a Cartesian product. It returns rows combining every row from the primary desk with every row of the second desk. For Example, if we be a part of two tables having 15 and 20 columns the Cartesian product of two tables will probably be 15×20=300 rows.

What is a schema in SQL?

Our database includes of plenty of totally different entities comparable to tables, saved procedures, features, database homeowners and so forth. To make sense of how all these totally different entities work together, we would want the assistance of schema. So, you may contemplate schema to be the logical relationship between all of the totally different entities that are current within the database.

Once we’ve got a transparent understanding of the schema, this helps in plenty of methods:

  • We can determine which consumer has entry to which tables within the database.
  • We can modify or add new relationships between totally different entities within the database.

Overall, you may contemplate a schema to be a blueprint for the database, which offers you the whole image of how totally different objects work together with one another and which customers have entry to totally different entities.

How to delete a column in SQL?

To delete a column in SQL we will probably be utilizing DROP COLUMN technique:

ALTER TABLE workers
DROP COLUMN age;

We will begin off by giving the key phrases ALTER TABLE, then we’ll give the identify of the desk, following which we’ll give the key phrases DROP COLUMN and at last give the identify of the column which we’d wish to take away.

What is a singular key in SQL?

Unique Key is a constraint in SQL. So, earlier than understanding what precisely is a major key, let’s perceive what precisely is a constraint in SQL. Constraints are the principles enforced on information columns on a desk. These are used to restrict the kind of information that may go right into a desk. Constraints can both be column degree or desk degree. 

Unique Key: 

Whenever we give the constraint of distinctive key to a column, this might imply that the column can not have any duplicate values current in it. In different phrases, all of the information that are current on this column should be distinctive.

How to implement a number of situations utilizing the WHERE clause?

We can implement a number of situations utilizing AND, OR operators:

SELECT * FROM workers WHERE first_name = ‘Steven’ AND wage <=10000;

In the above command, we’re giving two situations. The situation ensures that we extract solely these information the place the primary identify of the worker is ‘Steven’ and the second situation ensures that the wage of the worker is lower than $10,000. In different phrases, we’re extracting solely these information, the place the worker’s first identify is ‘Steven’ and this particular person’s wage must be lower than $10,000.

What is the distinction between SQL vs PL/SQL?

BASIS FOR COMPARISON SQL PL/SQL
Basic In SQL you may execute a single question or a command at a time. In PL/SQL you may execute a block of code at a time.
Full kind Structured Query Language Procedural Language, an extension of SQL.
Purpose It is sort of a supply of information that’s to be displayed. It is a language that creates an utility that shows information acquired by SQL.
Writes In SQL you may write queries and instructions utilizing DDL, DML statements. In PL/SQL you may write a block of code that has procedures, features, packages or variables, and so on.
Use Using SQL, you may retrieve, modify, add, delete, or manipulate the info within the database. Using PL/SQL, you may create purposes or server pages that show the knowledge obtained from SQL in a correct format.
Embed You can embed SQL statements in PL/SQL. You cannot embed PL/SQL in SQL

What is the distinction between SQL having vs the place?

S. No. Where Clause Having Clause
1 The WHERE clause specifies the standards which particular person information should meet to be chosen by a question. It can be utilized with out the GROUP by clause The HAVING clause can’t be used with out the GROUP BY clause
2 The WHERE clause selects rows earlier than grouping The HAVING clause selects rows after grouping
3 The WHERE clause can not comprise mixture features The HAVING clause can comprise mixture features
4 WHERE clause is used to impose a situation on SELECT assertion in addition to single row operate and is used earlier than GROUP BY clause HAVING clause is used to impose a situation on GROUP Function and is used after GROUP BY clause within the question
5 SELECT Column,AVG(Column_nmae)FROM Table_name WHERE Column > worth GROUP BY Column_nmae SELECT Columnq, AVG(Coulmn_nmae)FROM Table_name WHERE Column > worth GROUP BY Column_nmae Having column_name>or<worth

SQL Interview Questions for Experienced

Planning to modify your profession to SQL or simply must improve your place? Whatever your purpose, this part will higher put together you for the SQL interview. We have compiled a set of superior SQL questions that could be often requested throughout the interview. 

What is SQL injection?

SQL injection is a hacking method which is extensively utilized by black-hat hackers to steal information out of your tables or databases. Let’s say, if you happen to go to an internet site and provides in your consumer data and password, the hacker would add some malicious code over there such that, he can get the consumer data and password immediately from the database. If your database accommodates any very important data, it’s all the time higher to maintain it safe from SQL injection assaults.

What is a set off in SQL?

A set off is a saved program in a database which routinely offers responses to an occasion of DML operations executed by inserting, replace, or delete. In different phrases, is nothing however an auditor of occasions occurring throughout all database tables.

Let’s take a look at an instance of a set off:

CREATE TRIGGER bank_trans_hv_alert
	BEFORE UPDATE ON bank_account_transaction
	FOR EACH ROW
	start
	if( abs(:new.transaction_amount)>999999)THEN
    RAISE_APPLICATION_ERROR(-20000, 'Account transaction exceeding the each day deposit on SAVINGS account.');
	finish if;
	finish;

How to insert a number of rows in SQL?

To insert a number of rows in SQL we will observe the under syntax:

INSERT INTO table_name (column1, column2,column3...)
VALUES
    (value1, value2, value3…..),
    (value1, value2, value3….),
    ...
    (value1, value2, value3);

We begin off by giving the key phrases INSERT INTO then we give the identify of the desk into which we’d wish to insert the values. We will observe it up with the record of the columns, for which we must add the values. Then we’ll give within the VALUES key phrase and at last, we’ll give the record of values.

Here is an instance of the identical:

INSERT INTO workers (
    identify,
    age,
    wage)
VALUES
    (
        'Sam',
        21,
       75000
    ),
    (
        ' 'Matt',
        32,
       85000    ),

    (
        'Bob',
        26,
       90000
    );

In the above instance, we’re inserting a number of information into the desk referred to as workers.

How to seek out the nth highest wage in SQL?

This is how we will discover the nth highest wage in SQL SERVER utilizing TOP key phrase:

SELECT TOP 1 wage FROM ( SELECT DISTINCT TOP N wage FROM #Employee ORDER BY wage DESC ) AS temp ORDER BY wage

This is how we will discover the nth highest wage in MYSQL utilizing LIMIT key phrase:

SELECT wage FROM Employee ORDER BY wage DESC LIMIT N-1, 1

How to repeat desk in SQL?

We can use the SELECT INTO assertion to repeat information from one desk to a different. Either we will copy all the info or just some particular columns.

This is how we will copy all of the columns into a brand new desk:

SELECT *
INTO newtable
FROM oldtable
WHERE situation;

If we wish to copy just some particular columns, we will do it this fashion:

SELECT column1, column2, column3, ...
INTO newtable 
FROM oldtable
WHERE situation;

How so as to add a brand new column in SQL?

We can add a brand new column in SQL with the assistance of alter command:

ALTER TABLE workers ADD COLUMN contact INT(10);

This command helps us so as to add a brand new column named as contact within the workers desk.

How to make use of LIKE in SQL?

The LIKE operator checks if an attribute worth matches a given string sample. Here is an instance of LIKE operator

SELECT * FROM workers WHERE first_name like ‘Steven’; 

With this command, we can extract all of the information the place the primary identify is like “Steven”.

Yes, SQL server drops all associated objects, which exists inside a desk like constraints, indexex, columns, defaults and so on. But dropping a desk won’t drop views and sorted procedures as they exist exterior the desk. 

Can we disable a set off? If sure, How?

Yes, we will disable a single set off on the database through the use of “DISABLE TRIGGER triggerName ON<>. We also have an option to disable all the trigger by using, “DISABLE Trigger ALL ON ALL SERVER”.

What is a Live Lock?

A dwell lock is one the place a request for an unique lock is repeatedly denied as a result of a collection of overlapping shared locks preserve interferring. A dwell lock additionally happens when learn transactions create a desk or web page. 

How to fetch alternate information from a desk?

Records might be fetched for each Odd and Even row numbers – To show even numbers –

Select workerId from (Select rowno, workerId from worker) the place mod(rowno,2)=0 

To show odd numbers –

Select workerId from (Select rowno, workerId from worker) the place mod(rowno,2)=1

Define COMMIT and provides an instance?

When a COMMIT is utilized in a transaction, all modifications made within the transaction are written into the database completely.

Example:

BEGIN TRANSACTION; DELETE FROM HR.JobCandidate WHERE JobCandidateID = 20; COMMIT TRANSACTION; 

The above instance deletes a job candidate in a SQL server.

Can you be a part of the desk by itself? 

A desk might be joined to itself utilizing self be a part of, if you wish to create a consequence set that joins information in a desk with different information in the identical desk.

Explain Equi be a part of with an instance.

When two or extra tables have been joined utilizing equal to operator then this class known as an equi be a part of. Just we have to think about the situation is the same as (=) between the columns within the desk.

Example:

Select a.Employee_name,b.Department_name from Employee a,Employee b the place a.Department_ID=b.Department_ID

How will we keep away from getting duplicate entries in a question?

The SELECT DISTINCT is used to get distinct information from tables utilizing a question. The under SQL question selects solely the DISTINCT values from the “Country” column within the “Customers” desk:

SELECT DISTINCT Country FROM Customers;

How are you able to create an empty desk from an present desk?

Lets take an instance:

Select * into studentcopy from pupil the place 1=2 

Here, we’re copying the coed desk to a different desk with the identical construction with no rows copied.

Write a Query to show odd information from pupil desk?

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS RowID FROM pupil) WHERE row_id %2!=0

Explain Non-Equi Join with an instance?

When two or extra tables are becoming a member of the ultimate to situation, then that be a part of is named Non Equi Join. Any operator can be utilized right here, that’s <>,!=,<,>,Between.

Example:

Select b.Department_ID,b.Department_name from Employee a,Department b the place a.Department_id <> b.Department_ID;

How are you able to delete duplicate information in a desk with no major key?

By utilizing the SET ROWCOUNT command. It limits the variety of information affected by a command. Let’s take an instance, you probably have 2 duplicate rows, you’ll SET ROWCOUNT 1, execute DELETE command after which SET ROWCOUNT 0.

Difference between NVL and NVL2 features?

Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) features verify the worth exp1 to see whether it is null. With the NVL(exp1, exp2) operate, if exp1 isn’t null, then the worth of exp1 is returned; in any other case, the worth of exp2 is returned, however case to the identical information sort as that of exp1. With the NVL2(exp1, exp2, exp3) operate, if exp1 isn’t null, then exp2 is returned; in any other case, the worth of exp3 is returned.

What is the distinction between clustered and non-clustered indexes?

  1. Clustered indexes might be learn quickly slightly than non-clustered indexes. 
  2. Clustered indexes retailer information bodily within the desk or view whereas, non-clustered indexes don’t retailer information within the desk because it has separate construction from the info row.

What does this question says? GRANT privilege_name ON object_name TO PUBLIC [WITH GRANT OPTION];

The given syntax signifies that the consumer can grant entry to a different consumer too.

Where MyISAM desk is saved?

Each MyISAM desk is saved on disk in three recordsdata. 

  1. The “.frm” file shops the desk definition. 
  2. The information file has a ‘.MYD’ (MYData) extension. 
  3. The index file has a ‘.MYI’ (MYIndex) extension. 

What does myisamchk do?

It compresses the MyISAM tables, which reduces their disk or reminiscence utilization.

What is ISAM?

ISAM is abbreviated as Indexed Sequential Access Method. It was developed by IBM to retailer and retrieve information on secondary storage techniques like tapes.

What is Database White field testing?

White field testing contains: Database Consistency and ACID properties Database triggers and logical views Decision Coverage, Condition Coverage, and Statement Coverage Database Tables, Data Model, and Database Schema Referential integrity guidelines.

What are the several types of SQL sandbox?

There are 3 several types of SQL sandbox: 

  • Safe Access Sandbox: Here a consumer can carry out SQL operations comparable to creating saved procedures, triggers and so on. however can not have entry to the reminiscence in addition to can not create recordsdata.
  •  External Access Sandbox: Users can entry recordsdata with out having the proper to control the reminiscence allocation.
  • Unsafe Access Sandbox: This accommodates untrusted codes the place a consumer can have entry to reminiscence.

What is Database Black Box Testing?

This testing includes:

  • Data Mapping
  • Data saved and retrieved
  • Use of Black Box testing methods comparable to Equivalence Partitioning and Boundary Value Analysis (BVA).

Explain Right Outer Join with Example?

This be a part of is usable, when consumer needs all of the information from Right desk (Second desk) and solely equal or matching information from First or left desk. The unmatched information are thought of as null information. Example: Select t1.col1,t2.col2….t ‘n’col ‘n.’. from table1 t1,table2 t2 the place t1.col(+)=t2.col;

What is a Subquery?

A SubQuery is a SQL question nested into a bigger question. Example: SELECT employeeID, firstName, finalName FROM workers WHERE departmentID IN (SELECT departmentID FROM departments WHERE locationID = 2000) ORDER BY firstName, finalName; 

SQL Interview Questions for Developers

How to seek out duplicate information in SQL?

There are a number of methods to seek out duplicate information in SQL. Let’s see how can we discover duplicate information utilizing group by:

SELECT 
    x, 
    y, 
    COUNT(*) occurrences
FROM z1
GROUP BY
    x, 
    y
HAVING 
    COUNT(*) > 1;

We may also discover duplicates within the desk utilizing rank:

SELECT * FROM ( SELECT eid, ename, eage, Row_Number() OVER(PARTITION BY ename, eage ORDER By ename) AS Rank FROM workers ) AS X WHERE Rank>1

What is Case WHEN in SQL?

If you’ve got information about different programming languages, you then’d have learnt about if-else statements. You can contemplate Case WHEN to be analogous to that.

In Case WHEN, there will probably be a number of situations and we’ll select one thing on the idea of those situations.

Here is the syntax for CASE WHEN:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE consequence
END;

We begin off by giving the CASE key phrase, then we observe it up by giving a number of WHEN, THEN statements.

How to seek out 2nd highest wage in SQL?

Below is the syntax to seek out 2nd highest wage in SQL:

SELECT identify, MAX(wage)
  FROM workers
 WHERE wage < (SELECT MAX(wage)
                 FROM workers);

How to delete duplicate rows in SQL?

There are a number of methods to delete duplicate information in SQL.

Below is the code to delete duplicate information utilizing rank:

alter desk emp add  sid int id(1,1)

    delete e
    from  emp e
    inside be a part of
    (choose *,
    RANK() OVER ( PARTITION BY eid,ename ORDER BY id DESC )rank
    From emp )T on e.sid=t.sid
    the place e.Rank>1

    alter desk emp 
    drop  column sno

Below is the syntax to delete duplicate information utilizing groupby and min:

alter desk emp add  sno int id(1,1)
	
	    delete E from emp E
	    left be a part of
	    (choose min(sno) sno From emp group by empid,ename ) T on E.sno=T.sno
	    the place T.sno is null

	    alter desk emp 
	    drop  column sno	

What is cursor in SQL?

Cursors in SQL are used to retailer database tables. There are two sorts of cursors:

  • Implicit Cursor
  • Explicit Cursor

Implicit Cursor:

These implicit cursors are default cursors that are routinely created. A consumer can not create an implicit cursor.

Explicit Cursor:

Explicit cursors are user-defined cursors. This is the syntax to create specific cursor:

DECLARE cursor_name CURSOR FOR SELECT * FROM table_name

We begin off by giving by key phrase DECLARE, then we give the identify of the cursor, after that we give the key phrases CURSOR FOR SELECT * FROM, lastly, we give within the identify of the desk.

How to create a saved process utilizing SQL Server?

If you’ve got labored with different languages, you then would know in regards to the idea of Functions. You can contemplate saved procedures in SQL to be analogous to features in different languages. This signifies that we will retailer a SQL assertion as a saved process and this saved process might be invoked every time we would like.

This is the syntax to create a saved process:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

We begin off by giving the key phrases CREATE PROCEDURE, then we go forward and provides the identify of this saved process. After that, we give the AS key phrase and observe it up with the SQL question, which we would like as a saved process. Finally, we give the GO key phrase.

Once, we create the saved process, we will invoke it this fashion:

EXEC procedure_name;

We will give within the key phrase EXEC after which give the identify of the saved process.

Let’s take a look at an instance of a saved process:

CREATE PROCEDURE employee_location @location nvarchar(20)
AS
SELECT * FROM workers WHERE location = @location
GO;

In the above command, we’re making a saved process which is able to assist us to extract all the staff who belong to a specific location.

EXEC employee_location @location = 'Boston';

With this, we’re extracting all the staff who belong to Boston.

How to create an index in SQL?

We can create an index utilizing this command:

CREATE INDEX index_name
ON table_name (column1, column2, column3 ...);

We begin off by giving the key phrases CREATE INDEX after which we’ll observe it up with the identify of the index, after that we’ll give the ON key phrase. Then, we’ll give the identify of the desk on which we’d wish to create this index. Finally, in parenthesis, we’ll record out all of the columns which can have the index. Let’s take a look at an instance:

CREATE INDEX wage
ON Employees (Salary);

In the above instance, we’re creating an index referred to as a wage on high of the ‘Salary’ column of the ‘Employees’ desk.

Now, let’s see how can we create a singular index:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2,column3 ...);

We begin off with the key phrases CREATE UNIQUE INDEX, then give within the identify of the index, after that, we’ll give the ON key phrase and observe it up with the identify of the desk. Finally, in parenthesis, we’ll give the record of the columns which on which we’d need this distinctive index.

How to alter the column information sort in SQL?

We can change the info sort of the column utilizing the alter desk. This would be the command:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

We begin off by giving the key phrases ALTER TABLE, then we’ll give within the identify of the desk. After that, we’ll give within the key phrases MODIFY COLUMN. Going forward, we’ll give within the identify of the column for which we’d wish to change the datatype and at last we’ll give within the information sort to which we’d wish to change.

How to Rename Column Name in SQL?

Difference between SQL and NoSQL databases?

SQL stands for structured question language and is majorly used to question information from relational databases. When we discuss a SQL database, it is going to be a relational database. 

But with regards to the NoSQL databases, we will probably be working with non-relational databases.

Want to study extra about NoSQL databases? Check out the NoSQL course.

SQL Joins Interview Questions

How to alter column identify in SQL?

The command to alter the identify of a column is totally different in several RDBMS.

This is the command to alter the identify of a column in MYSQL:

ALTER TABLE Customer CHANGE Address Addr char(50);

IN MYSQL, we’ll begin off through the use of the ALTER TABLE key phrases, then we’ll give within the identify of the desk. After that, we’ll use the CHANGE key phrase and provides within the authentic identify of the column, following which we’ll give the identify to which we’d wish to rename our column.

This is the command to alter the identify of a column in ORACLE:

ALTER TABLE Customer RENAME COLUMN Address TO Addr;

In ORACLE, we’ll begin off through the use of the ALTER TABLE key phrases, then we’ll give within the identify of the desk. After that, we’ll use the RENAME COLUMN key phrases and provides within the authentic identify of the column, following which we’ll give the TO key phrase and at last give the identify to which we wish to rename our column.

When it involves SQL Server, it’s not doable to rename the column with the assistance of ALTER TABLE command, we must use sp_rename.

What is a view in SQL?

A view is a database object that’s created utilizing a Select Query with advanced logic, so views are mentioned to be a logical illustration of the bodily information, i.e Views behave like a bodily desk and customers can use them as database objects in any a part of SQL queries.

Let’s take a look at the sorts of Views:

  • Simple View
  • Complex View
  • Inline View
  • Materialized View

Simple View:

Simple views are created with a choose question written utilizing a single desk. Below is the command to create a easy view:

Create VIEW Simple_view as Select * from BANK_CUSTOMER ;

Complex View:

Create VIEW Complex_view as SELECT bc.customer_id , ba.bank_account From Bank_customer bc JOIN Bank_Account ba Where bc.customer_id = ba.customer_id And ba.steadiness > 300000

Inline View:

A subquery can also be referred to as an inline view if and solely whether it is referred to as in FROM clause of a SELECT question.

SELECT * FROM ( SELECT bc.customer_id , ba.bank_account From Bank_customer bc JOIN Bank_Account ba Where bc.customer_id = ba.customer_id And ba.steadiness > 300000)
view in SQL

How to drop a column in SQL?

To drop a column in SQL, we will probably be utilizing this command:

ALTER TABLE workers
DROP COLUMN gender;

We will begin off by giving the key phrases ALTER TABLE, then we’ll give the identify of the desk, following which we’ll give the key phrases DROP COLUMN and at last give the identify of the column which we’d wish to take away.

How to make use of BETWEEN in SQL?

The BETWEEN operator checks an attribute worth inside a variety. Here is an instance of BETWEEN operator:

SELECT * FROM workers WHERE wage between 10000 and 20000;

With this command, we can extract all of the information the place the wage of the worker is between 10000 and 20000.

Advanced SQL Interview Questions

What are the subsets of SQL?

  • DDL (Data Definition Language): Used to outline the info construction it consists of the instructions like CREATE, ALTER, DROP, and so on. 
  • DML (Data Manipulation Language): Used to control already present information within the database, instructions like SELECT, UPDATE, INSERT 
  • DCL (Data Control Language): Used to manage entry to information within the database, instructions like GRANT, REVOKE.

Difference between CHAR and VARCHAR2 datatype in SQL?

CHAR is used to retailer fixed-length character strings, and VARCHAR2 is used to retailer variable-length character strings.

How to type a column utilizing a column alias?

By utilizing the column alias within the ORDER BY as a substitute of the place clause for sorting

Difference between COALESCE() & ISNULL() ?

COALESCE() accepts two or extra parameters, one can apply 2 or as many parameters but it surely returns solely the primary non NULL parameter. 

ISNULL() accepts solely 2 parameters. 

The first parameter is checked for a NULL worth, whether it is NULL then the 2nd parameter is returned, in any other case, it returns the primary parameter.

What is “Trigger” in SQL?

A set off permits you to execute a batch of SQL code when an insert,replace or delete command is run in opposition to a particular desk as Trigger is claimed to be the set of actions which can be carried out every time instructions like insert, replace or delete are given. 

Write a Query to show worker particulars together with age.

SELECT * DATEDIFF(yy, dob, getdate()) AS 'Age' FROM worker

Write a Query to show worker particulars together with age?

SELECT SUM(wage) FROM worker

Write an SQL question to get the third most wage of an worker from a desk named employee_table.

SELECT TOP 1 wage FROM ( SELECT TOP 3 wage FROM employee_table ORDER BY wage DESC ) AS emp ORDER BY wage ASC; 

What are mixture and scalar features?

Aggregate features are used to guage mathematical calculations and return single values. This might be calculated from the columns in a desk. Scalar features return a single worth based mostly on enter worth. 

Example -. Aggregate – max(), depend – Calculated with respect to numeric. Scalar – UCASE(), NOW() – Calculated with respect to strings.

What is a impasse?

It is an undesirable state of affairs the place two or extra transactions are ready indefinitely for each other to launch the locks. 

Explain left outer be a part of with instance.

Left outer be a part of is beneficial if you’d like all of the information from the left desk(first desk) and solely matching information from 2nd desk. The unmatched information are null information. Example: Left outer be a part of with “+” operator Select t1.col1,t2.col2….t ‘n’col ‘n.’. from table1 t1,table2 t2 the place t1.col=t2.col(+);

What is SQL injection?

SQL injection is a code injection method used to hack data-driven purposes.

What is a UNION operator?

The UNION operator combines the outcomes of two or extra Select statements by eradicating duplicate rows. The columns and the info varieties should be the identical within the SELECT statements.

Explain SQL Constraints.

SQL Constraints are used to specify the principles of information sort in a desk. They might be specified whereas creating and altering the desk. The following are the constraints in SQL: NOT NULL CHECK DEFAULT UNIQUE PRIMARY KEY FOREIGN KEY

What is the ALIAS command?

This command gives one other identify to a desk or a column. It can be utilized within the WHERE clause of a SQL question utilizing the “as” key phrase. 

What are Group Functions? Why do we want them?

Group features work on a set of rows and return a single consequence per group. The popularly used group features are AVG, MAX, MIN, SUM, VARIANCE, and COUNT.

How can dynamic SQL be executed?

  • By executing the question with parameters 
  • By utilizing EXEC 
  • By utilizing sp_executesql

What is the utilization of NVL() operate?

This operate is used to transform the NULL worth to the opposite worth.

Write a Query to show worker particulars belongs to ECE division?

SELECT EmpNo, EmpName, Salary FROM worker WHERE deptNo in (choose deptNo from dept the place deptName = ‘ECE’)

What are the principle variations between #temp tables and @desk variables and which one is most popular?

1. SQL server can create column statistics on #temp tables. 

2. Indexes might be created on #temp tables 

3. @desk variables are saved in reminiscence as much as a sure threshold

What is CLAUSE?

SQL clause is outlined to restrict the consequence set by offering situations to the question. This normally filters some rows from the entire set of information. Example – Query that has WHERE situation.

What is a recursive saved process?

A saved process calls by itself till it reaches some boundary situation. This recursive operate or process helps programmers to make use of the identical set of code any variety of instances.

What does the BCP command do?

The Bulk Copy is a utility or a instrument that exports/imports information from a desk right into a file and vice versa. 

What is a Cross Join?

In SQL cross be a part of, a mixture of each row from the 2 tables is included within the consequence set. This can also be referred to as cross product set. For instance, if desk A has ten rows and desk B has 20 rows, the consequence set can have 10 * 20 = 200 rows offered there’s a NOWHERE clause within the SQL assertion.

Which operator is utilized in question for sample matching?

LIKE operator is used for sample matching, and it may be used as- 1. % – Matches zero or extra characters. 2. _(Underscore) – Matching precisely one character.

Write a SQL question to get the present date?

SELECT CURDATE();

State the case manipulation features in SQL?

  • LOWER: converts all of the characters to lowercase.
  • UPPER: converts all of the characters to uppercase. 
  • INITCAP: converts the preliminary character of every phrase to uppercase

How so as to add a column to an present desk?

ALTER TABLE Department ADD (Gender, M, F)

Define lock escalation?

A question first takes the bottom degree lock doable with the smallest row degree. When too many rows are locked, the lock is escalated to a variety or web page lock. If too many pages are locked, it might escalate to a desk lock. 

How to retailer Videos inside SQL Server desk?

By utilizing FILESTREAM datatype, which was launched in SQL Server 2008.

State the order of SQL SELECT?

The order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clauses are necessary.

What is the distinction between IN and EXISTS?

IN: Works on List consequence set Doesn’t work on subqueries leading to Virtual tables with a number of columns Compares each worth within the consequence record.

Exists: Works on Virtual tables Is used with co-related queries Exits comparability when the match is discovered

How do you copy information from one desk to a different desk?

INSERT INTO table2 (column1, column2, column3, …) SELECT column1, column2, column3, … FROM table1 WHERE situation;

List the ACID properties that guarantee that the database transactions are processed

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that assure that database transactions are processed reliably. 

What would be the output of the next Query, offered the worker desk has 10 information? 

BEGIN TRAN TRUNCATE TABLE Employees ROLLBACK SELECT * FROM Employees

This question will return 10 information as TRUNCATE was executed within the transaction. TRUNCATE doesn’t itself preserve a log however BEGIN TRANSACTION retains observe of the TRUNCATE command.

What do you imply by Stored Procedures? How will we use it?

A saved process is a set of SQL statements that can be utilized as a operate to entry the database. We can create these saved procedures earlier earlier than utilizing it and may execute them wherever required by making use of some conditional logic to it. Stored procedures are additionally used to cut back community site visitors and enhance efficiency.

What does GRANT command do?

This command is used to supply database entry to customers aside from the administrator in SQL privileges.

What does the First regular kind do?

First Normal Form (1NF): It removes all duplicate columns from the desk. It creates a desk for associated information and identifies distinctive column values.

How so as to add e report to the desk?

INSERT syntax is used so as to add a report to the desk. INSERT into table_name VALUES (value1, value2..);

What are the totally different tables current in MySQL?

There are 5 tables current in MYSQL.

  • MyISAM 
  • Heap 
  • Merge 
  • INNO DB 
  • ISAM

What is BLOB and TEXT in MySQL?

BLOB stands for the big binary objects. It is used to carry a variable quantity of information. TEXT is a case-insensitive BLOB. TEXT values are non-binary strings (character strings). 

What is the usage of mysql_close()?

Mysql_close() can’t be used to shut the persistent connection. Though it may be used to shut a connection opened by mysql_connect().

Write a question to seek out out the info between ranges?

In day-to-day actions, the consumer wants to seek out out the info between some vary. To obtain this consumer wants to make use of Between..and operator or Greater than and fewer than the operator. 

Query 1: Using Between..and operator
Select * from Employee the place wage between 25000 and 50000;
Query 2: Using operators (Greater than and fewer than)
Select * from Employee the place wage >= 25000 and wage <= 50000;

How to calculate the variety of rows in a desk with out utilizing the depend operate?

There are so many system tables that are crucial. Using the system desk consumer can depend the variety of rows within the desk. following question is useful in that case, Select table_name, num_rows from user_tables the place table_name=’Employee’;

What is unsuitable with the next question? SELECT empName FROM worker WHERE wage <> 6000

The following question won’t fetch a report with the wage of 6000 but additionally will skip the report with NULL. 

Will the next statements execute? if sure what will probably be output? SELECT NULL+1 SELECT NULL+’1′

Yes, no error. The output will probably be NULL. Performing any operation on NULL will get the NULL consequence.

SQL Server Interview Questions

What is an SQL server?

SQL server has stayed on high as one of the vital fashionable database administration merchandise ever since its first launch in 1989 by Microsoft Corporation. The product is used throughout industries to retailer and course of giant volumes of information. It was primarily constructed to retailer and course of information that’s constructed on a relational mannequin of information. 

SQL Server is extensively used for information evaluation and likewise scaling up of information. SQL Server can be utilized at the side of Big Data instruments comparable to Hadoop

SQL Server can be utilized to course of information from varied information sources comparable to Excel, Table, .Net Framework utility, and so on.

How to put in SQL Server?

  • Click on the under SQL Server official launch hyperlink to entry the most recent model: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  • Select the kind of SQL Server version that you just wish to set up. SQL Server can be utilized on a Cloud Platform or as an open-source version(Express or Developer) in your native laptop system. 
  • Click on the Download Now button.
  • Save the .exe file in your system. Right-click on the .exe file and click on on Open.
  • Click on ‘Yes’ to permit the modifications to be made in your system and have SQL Server Installed.
  • Once the set up is full, restart your system, if required, and launch the SQL Server Management Studio utility from the START menu.

How to create a saved process in SQL Server?

A Stored Procedure is nothing however a often used SQL question. Queries comparable to a SELECT question, which might typically be used to retrieve a set of knowledge many instances inside a database, might be saved as a Stored Procedure. The Stored Procedure, when referred to as, executes the SQL question saved inside the Stored Procedure.

Syntax to create a Stored Proc:

CREATE PROCEDURE PROCEDURE_NAME
AS
SQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE)
GO;

Stored procedures might be user-defined or built-in. Various parameters might be handed onto a Stored Procedure.

How to put in SQL Server 2008?

  • Click on the under SQL Server official launch hyperlink: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  • Click on the search icon and kind in – SQL Server 2008 obtain
  • Click on the consequence hyperlink to obtain and save SQL Server 2008.
  • Select the kind of SQL Server version that you just wish to set up. SQL Server can be utilized on a Cloud Platform or as an open-source version(Express or Developer) in your native laptop system.
  • Click on the Download Now button.
  • Save the .exe file in your system. Right-click on the .exe file and click on on Open.
  • Click on ‘Yes’ to permit the modifications to be made in your system and have SQL Server put in.
  • Once the set up is full, restart your system, if required, and launch the SQL Server Management Studio utility.

How to put in SQL Server 2017?

  • Click on the under SQL Server official launch hyperlink: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  • Click on the search icon and kind in – SQL Server 2017 obtain
  • Click on the consequence hyperlink to obtain and save SQL Server 2017.
  • Select the kind of SQL Server version that you just wish to set up. SQL Server can be utilized on a Cloud Platform or as an open-source version(Express or Developer) in your native laptop system.
  • Click on the Download Now button.
  • Save the .exe file in your system. Right-click on the .exe file and click on on Open.
  • Click on ‘Yes’ to permit the modifications to be made in your system and have SQL Server put in.
  • Once the set up is full, restart your system, if required, and launch the SQL Server Management Studio utility from the START menu.

How to revive the database in SQL Server?

Launch the SQL Server Management Studio utility and from the Object Explorer window pane, right-click on Databases and click on on Restore. This would routinely restore the database.

How to put in SQL Server 2014?

  • Click on the under SQL Server official launch hyperlink: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  • Click on the search icon and kind in – SQL Server 2014 obtain
  • Click on the consequence hyperlink to obtain and save SQL Server 2014.
  • Select the kind of SQL Server version that you just wish to set up. SQL Server can be utilized on a Cloud Platform or as an open-source version(Express or Developer) in your native laptop system.
  • Click on the Download Now button.
  • Save the .exe file in your system. Right-click on the .exe file and click on on Open.
  • Click on ‘Yes’ to permit the modifications to be made in your system and have SQL Server Installed.
  • Once the set up is full, restart your system, if required, and launch the SQL Server Management Studio utility from the START menu.

How to get the connection string from SQL Server?

Launch the SQL Server Management Studio. Go to the Database for which you require the Connection string. Right-click on the database and click on on Properties. In the Properties window that’s displayed, you may view the Connection String property.

Connection strings assist join databases to a different staging database or any exterior supply of information.

How to put in SQL Server 2012?

  • Click on the under SQL Server official launch hyperlink: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  • Click on the search icon and kind in – SQL Server 2012 obtain
  • Click on the consequence hyperlink to obtain and save SQL Server 2012.
  • Select the kind of SQL Server version that you just wish to set up. SQL Server can be utilized on a Cloud Platform or as an open-source version(Express or Developer) in your native laptop system.
  • Click on the Download Now button.
  • Save the .exe file in your system. Right-click on the .exe file and click on on Open.
  • Click on ‘Yes’ to permit the modifications to be made in your system and have SQL Server Installed.
  • Once the set up is full, restart your system, if required, and launch the SQL Server Management Studio utility from the START menu.

What is cte in SQL Server?

CTEs are Common Table Expressions which can be used to create short-term consequence tables from which information might be retrieved/ used. The commonplace syntax for a CTE with a SELECT assertion is:

WITH RESULT AS 
(SELECT COL1, COL2, COL3
FROM EMPLOYEE)
SELECT COL1, COL2 FROM RESULT
CTEs can be utilized with Insert, Update or Delete statements as properly.

Few examples of CTEs are given under:

Query to seek out the ten highest salaries.

with consequence as 

(choose distinct wage, dense_rank() over (order by wage desc) as wage rank from workers)
choose consequence. wage from consequence the place the consequence.salaryrank = 10

 

Query to seek out the twond highest wage

with the consequence as 

(choose distinct wage, dense_rank() over (order by wage desc) as salaryrank from workers)
choose consequence. wage from consequence the place the consequence.salaryrank = 2

In this fashion, CTEs can be utilized to seek out the nth highest wage inside an organisation.

How to alter the SQL Server password?

Launch your SQL Server Management Studio. Click on the Database connection for which you wish to change the login password. Click on Security from the choices that get displayed. 

Click on Logins and open your database connection. Type within the new password for login and click on on ‘OK’ to use the modifications. 

How to delete duplicate information in SQL Server?

Select the duplicate information in a desk HAVING COUNT(*)>1 

Add a delete assertion to delete the duplicate information.

Sample Query to seek out the duplicate information in a table-

(SELECT COL1, COUNT(*) AS DUPLICATE
FROM EMPLOYEE
GROUP BY COL1
HAVING COUNT(*) > 1)

How to uninstall SQL Server?

In Windows 10, go to the START menu and find the SQL Server.

Right-click and choose uninstall to uninstall the appliance.

How to verify SQL Server model?

You can run the under question to view the present model of SQL Server that you’re utilizing.

How to rename column identify in SQL Server?

From the Object Explorer window pane, go to the desk the place the column is current and select Design. Under the Column Name, choose the identify you wish to rename and enter the brand new identify. Go to the File menu and click on Save. 

What is the saved process in SQL Server?

A Stored Procedure is nothing however a often used SQL question. Queries comparable to a SELECT question, which might typically be used to retrieve a set of knowledge many instances inside a database, might be saved as a Stored Procedure. The Stored Procedure, when referred to as, executes the SQL question saved inside the Stored Procedure.

Syntax to create a Stored Proc:

CREATE PROCEDURE PROCEDURE_NAME
AS
SQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE)
GO;

You can execute the Stored Proc through the use of the command Exec Procedure_Name;

How to create a database in SQL Server?

After putting in the required model of SQL Server, it’s simple to create new databases and keep them. 

  1. Launch the SQL Server Management Studio
  2. In the Object Explorer window pane, right-click on Databases and choose ‘New Database’
  3. Enter the Database Name and click on on ‘Ok’.
  4. Voila! Your new database is prepared to be used.

What is an index in SQL Server?

Indexes are database objects which assist in retrieving information rapidly and extra effectively. Column indexes might be created on each Tables and Views. By declaring a Column as an index inside a desk/ view, the consumer can entry these information rapidly by executing the index. Indexes with a couple of column are referred to as Clustered indexes.

Syntax:

CREATE INDEX INDEX_NAME
ON TABLE_NAME(COL1, COL2);

The syntax to drop an Index is DROP INDEX INDEX_NAME;

Indexes are recognized to enhance the effectivity of SQL Select queries. 

How to create the desk in SQL Server?

Tables are the elemental storage objects inside a database. A desk is normally made up of 

Rows and Columns. The under syntax can be utilized to create a brand new desk with 3 columns.

CREATE TABLE TABLE_NAME(
COLUMN1 DATATYPE, 
COLUMN2 DATATYPE, 
COLUMN3 DATATYPE
);

Alternatively, you may right-click on Table within the Object Explorer window pane and choose ‘New -> Table’.

You may also outline the kind of Primary/ Foreign/ Check constraint when making a desk.

How to hook up with SQL Server?

  • Launch the SQL Server Management Studio from the START menu.
  • In the dialogue field proven under, choose the Server Type as Database Engine and Server Name because the identify of your laptop computer/ desktop system.
  • Select the suitable Authentication sort and click on on the Connect button.
  • A safe connection could be established, and the record of the out there Databases will probably be loaded within the Object Explorer window pane.

How to delete duplicate rows in SQL Server?

Select the duplicate information in a desk HAVING COUNT(*)>1 

Add a delete assertion to delete the duplicate information.

Sample Query to seek out the duplicate information in a desk –

(SELECT COL1, COUNT(*) AS DUPLICATE
FROM EMPLOYEE
GROUP BY COL1
HAVING COUNT(*) > 1);

How to obtain SQL Server?

The Express and Developer variations (open-source variations) of the most recent SQL Server launch might be downloaded from the official Microsoft web site. The hyperlink is given under for reference.
https://www.microsoft.com/en-in/sql-server/sql-server-downloads

How to attach SQL Server administration studio to the native database?

  • Launch the SQL Server Management Studio from the START menu.
  • In the dialogue field proven under, choose the Server Type as Database Engine and Server Name because the identify of your laptop computer/ desktop system and click on on the Connect button.
  • Select the Authentication as ‘Windows Authentication.
  • A safe connection could be established, and the record of the out there Databases will probably be loaded within the Object Explorer window pane.

How to obtain SQL Server 2014?

  • Both the Express and Developer variations (free editions) of SQL Server might be downloaded from the official Microsoft web site. The hyperlink is given under for reference.
  • Click on the hyperlink under: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  • Click on the search icon and kind in – SQL Server 2014 obtain
  • Click on the consequence hyperlink to obtain and save SQL Server 2014.

How to uninstall SQL Server 2014?

From the START menu, sort SQL Server. Right-click on the app and choose uninstall to uninstall the appliance out of your system. Restart the system, if required, for the modifications to get affected. 

How to seek out server names in SQL Server?

Run the question SELECT @@model; to seek out the model and identify of the SQL Server you might be utilizing. 

How to start out SQL Server?

Launch the SQL Server Management Studio from the START menu. Login utilizing Windows Authentication. In the Object Explorer window pane, you may view the record of databases and corresponding objects. 

What is the case when in SQL Server?

Case When statements in SQL are used to run by means of many situations and to return a worth when one such situation is met. If not one of the situations is met within the When statements, then the worth talked about within the Else assertion is returned. 

Syntax:

CASE
WHEN CONDITION1 THEN RESULT1

WHEN CONDITION2 THEN RESULT2

ELSE
RESULT
END;

Sample question:

HOW MANY HEAD OFFICES/ BRANCHES ARE THERE IN CANADA

choose 
sum ( 
case 
when region_id >=  5 AND region_id <= 7 then  
1
else 
0
finish ) as Canada
from company_regions;
Nested CASE assertion:
SELECT
SUM (
CASE
WHEN rental_rate = 0.99 THEN
1
ELSE
0
END
) AS "Mass",
SUM (
CASE
WHEN rental_rate = 2.99 THEN
1
ELSE
0
END
) AS "Economic",
SUM (
CASE
WHEN rental_rate = 4.99 THEN
1
ELSE
0
END
) AS " Luxury"
FROM
movie;

How to put in SQL Server administration studio?

Launch Google and within the Search toolbar, sort in SQL Server Management Studio obtain. 

Go to the routed web site and click on on the hyperlink to obtain. Once the obtain is full, open the .exe file to put in the content material of the file. Once the set up is full, refresh or restart the system, as required.

Alternatively, as soon as SQL Server is put in and launched, it’s going to immediate the consumer with an choice to launch SQ Server Management Studio. 

How to write down a saved process in SQL Server?

A Stored Procedure is nothing however a often used SQL question. Queries comparable to a SELECT question, which might typically be used to retrieve a set of knowledge many instances inside a database, might be saved as a Stored Procedure. The Stored Procedure, when referred to as, executes the SQL question saved inside the Stored Procedure.

Syntax to create a Stored Proc:

CREATE PROCEDURE PROCEDURE_NAME
AS
SQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE)
GO;

You can execute the Stored Proc through the use of the command Exec Procedure_Name;

How to open SQL Server?

Launch the SQL Server Management Studio from the START menu. Login utilizing Windows Authentication. In the Object Explorer window pane, you may view the record of databases and corresponding objects. 

How to make use of SQL Server?

SQL Server is used to retrieve and course of varied information that’s constructed on a relational mannequin.

Some of the widespread actions that may be taken on the info are CREATE, DELETE, INSERT, UPDATE, SELECT, REVOKE, and so on.

SQL Server may also be used to import and export information from totally different information sources. SQL Server may also be related to numerous different databases/ .Net frameworks utilizing Connection Strings.

SQL Server may also be used at the side of Big Data instruments like Hadoop. 

What is a operate in SQL Server?

Functions are pre-written codes that return a worth and which assist the consumer obtain a specific job regarding viewing, manipulating, and processing information.

Examples of some features are:

AGGREGATE FUNCTIONS:

  • MIN()- Returns the minimal worth
  • MAX()- Returns the utmost worth
  • AVG()- Returns the typical worth
  • COUNT()

STRING FUNCTIONS:

  • COALESCE()
  • CAST()
  • CONCAT()
  • SUBSTRING()

DATE FUNCTIONS:

  • GETDATE()
  • DATEADD()
  • DATEDIFF()

There are many sorts of features comparable to Aggregate Functions, Date Functions, String Functions, Mathematical features, and so on.

How to seek out nth highest wage in SQL Server with out utilizing a subquery

Query to seek out the ten highest salaries. For up-gradation of the b10 band.

with consequence as 

(choose distinct wage, dense_rank() over (order by wage desc) as salaryrank from workers)
choose consequence.wage from consequence the place consequence.salaryrank = 10

Query to seek out the twond highest wage

with the consequence as 

(choose distinct wage, dense_rank() over (order by wage desc) as wage rank from workers)
choose consequence.wage from consequence the place consequence.salaryrank = 2

In this fashion, by changing the wage rank worth, we will discover the nth highest wage in any organisation.

How to put in SQL Server in Windows 10?

Click on the under SQL Server official launch hyperlink: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
Click on the search icon and kind in - SQL Server 2012 obtain
Click on the consequence hyperlink to obtain and save SQL Server 2012.
Select the kind of the SQL Server version that you just wish to set up. SQL Server can be utilized on a Cloud Platform or as an open-source version(Express or Developer) in your native laptop system.
Click on the Download Now button.
Save the .exe file in your system. Right-click on the .exe file and click on on Open.
Click on ‘Yes’ to permit the modifications to be made in your system and have SQL Server Installed

How to create a temp desk in SQL Server?

Temporary tables can be utilized to retain the construction and a subset of information from the unique desk from which they had been derived. 

Syntax:

SELECT COL1, COL2
INTO TEMPTABLE1
FROM ORIGTABLE;

Temporary tables don’t occupy any bodily reminiscence and can be utilized to retrieve information quicker.

PostgreSQL Interview Questions

What is PostgreSQL?

PostgreSQL is without doubt one of the most generally and popularly used languages for Object-Relational Database Management techniques. It is especially used for giant net purposes. It is an open-source, object-oriented, -relational database system. It is extraordinarily highly effective and permits customers to increase any system with out drawback. It extends and makes use of the SQL language together with varied options for safely scaling and storage of intricate information workloads.

List totally different datatypes of PostgreSQL?

Listed under are among the new information varieties in PostgreSQL

  • UUID
  • Numeric varieties
  • Boolean
  • Character varieties
  • Temporal varieties
  • Geometric primitives
  • Arbitrary precision numeric
  • XML
  • Arrays and so on

What are the Indices of PostgreSQL?

Indices in PostgreSQL enable the database server to seek out and retrieve particular rows in a given construction. Examples are B-tree, hash, GiST, SP-GiST, GIN and BRIN.  Users may also outline their indices in PostgreSQL. However, indices add overhead to the info manipulation operations and are seldom used

What are tokens in PostgreSQL?

Tokens in PostgreSQL act because the constructing blocks of a supply code. They are composed of varied particular character symbols. Commands are composed of a collection of tokens and terminated by a semicolon(“;”). These could be a fixed, quoted identifier, different identifiers, key phrase or a relentless. Tokens are normally separated by whitespaces.

How to create a database in PostgreSQL?

Databases might be created utilizing 2 strategies 

  • First is the CREATE DATABASE SQL Command

We can create the database through the use of the syntax:-

CREATE DATABASE <dbname>;
  • The second is through the use of the createdb command

We can create the database through the use of the syntax:-

createdb [option...] <dbname> 

Are you an aspiring SQL Developer? A profession in SQL has seen an upward development in 2023, and you may be part of the ever-so-growing neighborhood. So, in case you are able to indulge your self within the pool of data and be ready for the upcoming SQL interview, then you might be on the proper place. …

180+ SQL Interview Questions and Answers in 2023 Read More »

The publish 180+ SQL Interview Questions and Answers in 2023 appeared first on Great Learning Blog: Free Resources what Matters to form your Career!.

Various choices might be taken by the createDB command based mostly on the use case.

How to create a desk in PostgreSQL?

You can create a brand new desk by specifying the desk identify, together with all column names and their varieties:

CREATE TABLE [IF NOT EXISTS] table_name (
column1 datatype(size) column_contraint,
column2 datatype(size) column_contraint,
.
.
.
columnn datatype(size) column_contraint,
table_constraints
);

How can we modify the column datatype in PostgreSQL?

The column the info sort might be modified in PostgreSQL through the use of the ALTER TABLE command:

ALTER TABLE table_name
ALTER COLUMN column_name1 [SET DATA] TYPE new_data_type,
ALTER COLUMN column_name2 [SET DATA] TYPE new_data_type,
...;

Compare ‘PostgreSQL’ with ‘MongoDB’

PostgreSQL MongoDB
PostgreSQL is an SQL database the place information is saved as tables, with structured rows and columns. It helps ideas like referential integrity entity-relationship and JOINS. PostgreSQL makes use of SQL as its querying language. PostgreSQL helps vertical scaling. This signifies that it’s worthwhile to use huge servers to retailer information. This results in a requirement of downtime to improve. It works higher if you happen to require relational databases in your utility or must run advanced queries that take a look at the restrict of SQL. MongoDB, alternatively, is a NoSQL database. There isn’t any requirement for a schema, due to this fact it may possibly retailer unstructured information. Data is saved as BSON paperwork and the doc’s construction might be modified by the consumer. MongoDB makes use of JavaScript for querying. It helps horizontal scaling, because of which extra servers might be added as per the requirement with minimal to no downtime.  It is suitable in a use case that requires a extremely scalable distributed database that shops unstructured information

What is Multi-Version concurrency management in PostgreSQL?

MVCC or higher often known as Multi-version concurrency management is used to implement transactions in PostgreSQL. It is used to keep away from undesirable locking of a database within the system. whereas querying a database every transaction sees a model of the database. This avoids viewing inconsistencies within the information, and likewise gives transaction isolation for each database session. MVCC locks for studying information don’t battle with locks acquired for

How do you delete the database in PostgreSQL?

Databases might be deleted in PostgreSQL utilizing the syntax

DROP DATABASE [IF EXISTS] <database_name>;

Please observe that solely databases having no lively connections might be dropped.

What does a schema comprise?

  • Schemas are part of the database that accommodates tables. They additionally comprise other forms of named objects, like information varieties, features, and operators.
  • The object names can be utilized in several schemas with out battle; Unlike databases, schemas are separated extra flexibly. This signifies that a consumer can entry objects in any of the schemas within the database they’re related to, until they’ve privileges to take action.
  • Schemas are extremely helpful when there’s a want to permit many customers entry to at least one database with out interfering with one another. It helps in organizing database objects into logical teams for higher manageability. Third-party purposes might be put into separate schemas to keep away from conflicts based mostly on names.

What is the sq. root operator in PostgreSQL?

It is denoted by ‘|/” and returns the sq. root of a quantity. Its syntax is

Egs:- Select |/16

How are the stats up to date in Postgresql?

To replace statistics in PostgreSQL a particular operate referred to as an specific ‘vacuum’ name is made. Entries in pg_statistic are up to date by the ANALYZE and VACUUM ANALYZE instructions

What Is A Candid?

The CTIDs area exists in each PostgreSQL desk. It is exclusive for each report of a desk and precisely reveals the situation of a tuple in a specific desk. A logical row’s CTID modifications when it’s up to date, thus it can’t be used as a everlasting row identifier. However, it’s helpful when figuring out a row inside a transaction when no replace is anticipated on the info merchandise.

What is Cube Root Operator (||/) in PostgreSQL?

It is denoted by ‘|/” and returns the sq. root of a quantity. Its syntax is

Egs:- Select |/16

Explain Write-Ahead Logging?

Write-ahead logging is a technique to make sure information integrity. It is a protocol that ensures writing the actions in addition to modifications right into a transaction log. It is thought to extend the reliability of databases by logging modifications earlier than they’re utilized or up to date onto the database. This gives a backup log for the database in case of a crash.

What is a non-clustered index?

A non-clustered index in PostgreSQL is an easy index, used for quick retrieval of information, with no certainty of the individuality of information. It additionally accommodates tips that could areas the place different elements of information are saved

How is safety ensured in PostgreSQL?

PostgreSQL makes use of 2 ranges of safety

  • Network-level safety makes use of Unix Domain sockets, TCP/IP sockets, and firewalls.
  • Transport-level safety which makes use of SSL/TLS to allow safe communication with the database
  • Database-level safety with options like roles and permissions, row-level safety (RLS), and auditing.

SQL Practice Questions

PART 1

This covers SQL primary question operations like creating databases varieties scratch, making a desk, inserting values and so on.

It is best to get hands-on as a way to have sensible expertise with SQL queries. A small error/bug will make you are feeling stunned and subsequent time you’ll get there!

Let’s get began!

1) Create a Database financial institution

CREATE DATABASE financial institution;
use financial institution

2) Create a desk with the identify “bank_details” with the next columns

— Product  with string information sort 

— Quantity with numerical information sort 

— Price with actual quantity information sort 

— purchase_cost with decimal information sort 

— estimated_sale_price with information sort float 

Create desk bank_details(
Product CHAR(10) , 
amount INT,
value Real ,
purchase_cost Decimal(6,2),
estimated_sale_price  Float); 

3) Display all columns and their datatype and measurement in Bank_details

4) Insert two information into Bank_details.

— 1st report with values —

— Product: PayCard

— Quantity: 3 

— value: 330

— Puchase_cost: 8008

— estimated_sale_price: 9009

— Product: PayPoints —

— Quantity: 4

— value: 200

— Puchase_cost: 8000

— estimated_sale_price: 6800

Insert into Bank_detailsvalues ( 'paycard' , 3 , 330, 8008, 9009);
Insert into Bank_detailsvalues ( 'paypoints' , 4 , 200, 8000, 6800);

5) Add a column: Geo_Location to the present Bank_details desk with information sort varchar and measurement 20

Alter desk Bank_details add  geo_location Varchar(20);

6) What is the worth of Geo_location for a product : “PayCard”?

Select geo_location  from Bank_details the place Product="PayCard";

7) How many characters does the  Product : “paycard” have within the Bank_details desk.

choose char_length(Product) from Bank_details the place Product="PayCard";

8) Alter the Product area from CHAR to VARCHAR in Bank_details

Alter desk  bank_details modify PRODUCT varchar(10);

9) Reduce the scale of the Product area from 10 to six and verify whether it is doable

Alter desk bank_details modify product varchar(6);

10) Create a desk named as Bank_Holidays with under fields 

— a) Holiday area which shows solely date 

— b) Start_time area which shows hours and minutes 

— c) End_time area which additionally shows hours and minutes and timezone

Create desk bank_holidays (
			Holiday  date ,
			Start_time datetime ,
			End_time timestamp);

11) Step 1: Insert at present’s date particulars in all fields of Bank_Holidays 

— Step 2: After step1, carry out the under 

— Postpone Holiday to subsequent day by updating the Holiday area

-- Step1: 
Insert into bank_holidays  values ( current_date(), 
         current_date(), 
current_date() );

-- Step 2: 
Update bank_holidays 
set vacation = DATE_ADD(Holiday , INTERVAL 1 DAY);

Update the End_time with present European time.

Update Bank_Holidays Set End_time = utc_timestamp();

12)  Display output of PRODUCT area as NEW_PRODUCT in  Bank_details desk

Select PRODUCT as NEW_PRODUCT from bank_details;

13)  Display just one report from bank_details

Select * from Bank_details restrict 1;

15) Display the primary 5 characters of the Geo_location area of Bank_details.

SELECT substr(Geo_location  , 1, 5)  FROM `bank_details`;

PART 2

— ——————————————————–

# Datasets Used: cricket_1.csv, cricket_2.csv

— cricket_1 is the desk for cricket take a look at match 1.

— cricket_2 is the desk for cricket take a look at match 2.

— ——————————————————–

Find all of the gamers who had been current within the take a look at match 1 in addition to within the take a look at match 2.

SELECT * FROM cricket_1
UNION
SELECT * FROM cricket_2;

Write a MySQl question to seek out the gamers from the take a look at match 1 having recognition larger than the typical recognition.

choose player_name , Popularity from cricket_1 WHERE Popularity > (SELECT AVG(Popularity) FROM cricket_1);

  Find player_id and participant identify which can be widespread within the take a look at match 1 and take a look at match 2.

SELECT player_id , player_name FROM cricket_1
WHERE cricket_1.player_id IN (SELECT player_id FROM cricket_2);

Retrieve player_id, runs, and player_name from cricket_1 and cricket_2 desk and show the player_id of the gamers the place the runs are greater than the typical runs.

SELECT player_id , runs , player_name FROM cricket_1 WHERE  cricket_1.RUNS > (SELECT AVG(RUNS) FROM cricket_2);


Write a question to extract the player_id, runs and player_name from the desk “cricket_1” the place the runs are higher than 50.

SELECT player_id , runs , player_name FROM cricket_1 
WHERE cricket_1.Runs > 50 ;

Write a question to extract all of the columns from cricket_1 the place player_name begins with ‘y’ and ends with ‘v’.

SELECT * FROM cricket_1 WHERE player_name LIKE 'ypercentv';

Write a question to extract all of the columns from cricket_1 the place player_name doesn’t finish with ‘t’.

SELECT * FROM cricket_1 WHERE player_name NOT LIKE '%t';
 

# Dataset Used: cric_combined.csv

Write a MySQL question to create a brand new column PC_Ratio that accommodates the recognition to charisma ratio.

ALTER TABLE cric_combined
ADD COLUMN PC_Ratio float4;

UPDATE cric_combined SET PC_Ratio =  (Popularity / Charisma);

 Write a MySQL question to seek out the highest 5 gamers having the best recognition to charisma ratio

SELECT Player_Name , PC_Ratio  FROM cric_combined ORDER BY  PC_Ratio DESC LIMIT 5;

Write a MySQL question to seek out the player_ID and the identify of the participant that accommodates the character “D” in it.

SELECT Player_Id ,  Player_Name FROM cric_combined WHERE Player_Name LIKE '%d%'; 

Dataset Used: new_cricket.csv

Extract the Player_Id and Player_name of the gamers the place the charisma worth is null.

SELECT Player_Id , Player_Name FROM new_cricket WHERE Charisma  IS NULL;

Write a MySQL question to impute all of the NULL values with 0.

SELECT IFNULL(Charisma, 0) FROM new_cricket;

Separate all Player_Id into single numeric ids (instance PL1 =  1).

SELECT Player_Id, SUBSTR(Player_Id,3)
FROM  new_cricket;

Write a MySQL question to extract Player_Id, Player_Name and charisma the place the charisma is larger than 25.

SELECT Player_Id , Player_Name , charisma FROM new_cricket WHERE charisma > 25;

# Dataset Used: churn1.csv

Write a question to depend all of the duplicate values from the column “Agreement” from the desk churn1.

SELECT Agreement, COUNT(Agreement) FROM churn1 GROUP BY Agreement HAVING COUNT(Agreement) > 1;

Rename the desk churn1 to “Churn_Details”.

RENAME TABLE churn1 TO Churn_Details;

Write a question to create a brand new column new_Amount that accommodates the sum of TotalAmount and MonthlyServiceCharges.

ALTER TABLE Churn_Details
ADD COLUMN new_Amount FLOAT;
UPDATE Churn_Details SET new_Amount = (TotalAmount + MonthlyServiceCharges);

SELECT new_Amount FROM CHURN_DETAILS;

 Rename column new_Amount to Amount.

ALTER TABLE Churn_Details CHANGE new_Amount Amount FLOAT;

SELECT AMOUNT FROM CHURN_DETAILS;

Drop the column “Amount” from the desk “Churn_Details”.

ALTER TABLE Churn_Details DROP COLUMN Amount ;

Write a question to extract the customerID, InternetConnection and gender from the desk “Churn_Details ” the place the worth of the column “InternetConnection” has ‘i’ on the second place.

SELECT customerID, InternetConnection,  gender FROM Churn_Details WHERE InternetConnection LIKE '_i%';


Find the information the place the tenure is 6x, the place x is any quantity.

SELECT * FROM Churn_Details WHERE tenure LIKE '6_';

Part 3

# DataBase = Property Price Train

Dataset used: Property_Price_Train_new

Write An MySQL Query To Print The First Three Characters Of  Exterior1st From Property_Price_Train_new Table.

Select substring(Exterior1st,1,3) from Property_Price_Train_new;


Write An MySQL Query To Print Brick_Veneer_Area Of Property_Price_Train_new Excluding Brick_Veneer_Type, “None” And “BrkCmn” From Property_Price_Train_new Table.

Select  Brick_Veneer_Area, Brick_Veneer_Type from Property_Price_Train_new  the place Brick_Veneer_Type not in ('None','BrkCmn');


Write An MySQL Query to print Remodel_Year , Exterior2nd of the Property_Price_Train_new Whose Exterior2nd Contains ‘H’.

Select Remodel_Year , Exterior2nd from Property_Price_Train_new the place Exterior2nd like '%H%' ;

Write MySQL question to print particulars of the desk Property_Price_Train_new whose Remodel_year from 1983 to 2006

choose * from Property_Price_Train_new the place Remodel_Year between 1983 and 2006;

Write MySQL question to print particulars of Property_Price_Train_new whose Brick_Veneer_Type ends with e and accommodates 4 alphabets.

Select * from Property_Price_Train_new the place Brick_Veneer_Type like '____e';

Write MySQl question to print nearest largest integer worth of column Garage_Area from Property_Price_Train_new

Select ceil(Garage_Area) from Property_Price_Train_new;

Fetch the three highest worth of column Brick_Veneer_Area from Property_Price_Train_new desk

Select Brick_Veneer_Area from Property_Price_Train_new order by Brick_Veneer_Area desc restrict 2,1;

Rename column LowQualFinSF to Low_Qual_Fin_SF fom desk Property_Price_Train_new

Alter desk Property_Price_Train_new change LowQualFinSF Low_Qual_Fin_SF varchar(150);

Convert Underground_Full_Bathroom (1 and 0) values to true or false respectively.

# Eg. 1 – true ; 0 – false

SELECT CASE WHEN Underground_Full_Bathroom = 0 THEN 'false' ELSE 'true' END FROM Property_Price_Train_new;

Extract complete Sale_Price for every year_sold column of Property_Price_Train_new desk.

Select Year_Sold, sum(Sale_Price) from Property_Price_Train_new group by Year_Sold;

Extract all adverse values from W_Deck_Area

Select W_Deck_Area from Property_Price_Train_new the place W_Deck_Area < 0;


Write MySQL question to extract Year_Sold, Sale_Price whose value is larger than 100000.

Select Sale_Price , Year_Sold from Property_Price_Train_new group by Year_Sold having Sale_Price  >  100000;

Write MySQL question to extract Sale_Price and House_Condition from Property_Price_Train_new and Property_price_train_2 carry out inside be a part of. Rename the desk as PPTN and PPTN2.

Select Sale_Price , House_Condition from Property_Price_Train_new AS PPTN inside be a part of Property_price_train_2 AS PPT2 on PPTN.ID= PPTN2.ID;

Count all duplicate values of column Brick_Veneer_Type from tbale Property_Price_Train_new

Select Brick_Veneer_Type, depend(Brick_Veneer_Type) from Property_Price_Train_new group by Brick_Veneer_Type having depend(Brick_Veneer_Type) > 1;

# DATABASE Cricket

Find all of the gamers from each matches.

SELECT * FROM cricket_1
UNION
SELECT * FROM cricket_2;

Perform proper be a part of on cricket_1 and cricket_2.

SELECT
    cric2.Player_Id, cric2.Player_Name, cric2.Runs, cric2.Charisma, cric1.Popularity
FROM
    cricket_1 AS cric1
        RIGHT JOIN
    cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

 Perform left be a part of on cricket_1 and cricket_2

SELECT
 cric1.Player_Id, cric1.Player_Name, cric1.Runs, cric1.Popularity, cric2.Charisma
FROM
    cricket_1 AS cric1
        LEFT JOIN
    cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

Perform left be a part of on cricket_1 and cricket_2.

SELECT
    cric1.Player_Id, cric1.Player_Name, cric1.Runs, cric1.Popularity, cric2.Charisma
FROM
    cricket_1 AS cric1
        INNER JOIN
    cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

Create a brand new desk and insert the consequence obtained after performing inside be a part of on the 2 tables cricket_1 and cricket_2.

CREATE TABLE Players1And2 AS
SELECT
    cric1.Player_Id, cric1.Player_Name, cric1.Runs, cric1.Popularity, cric2.Charisma
FROM
    cricket_1 AS cric1
        INNER JOIN
    cricket_2 AS cric2 ON cric1.Player_Id = cric2.Player_Id;

Write MySQL question to extract most runs of gamers get solely high two gamers

choose Player_Name, Runs from cricket_1 group by Player_Name having max(Runs) restrict 2;

PART 4

# Pre-Requisites

# Assuming Candidates are conversant in “Group by” and “Grouping functions” as a result of these are used together with JOINS within the questionnaire. 

# Create under DB objects 

CREATE TABLE BANK_CUSTOMER ( customer_id INT ,
             	customer_name VARCHAR(20),
             	Address 	VARCHAR(20),
             	state_code  VARCHAR(3) ,    	 
             	Telephone   VARCHAR(10)	);
INSERT INTO BANK_CUSTOMER VALUES (123001,"Oliver", "225-5, Emeryville", "CA" , "1897614500");
INSERT INTO BANK_CUSTOMER VALUES (123002,"George", "194-6,New brighton","MN" , "1897617000");
INSERT INTO BANK_CUSTOMER VALUES (123003,"Harry", "2909-5,walnut creek","CA" , "1897617866");
INSERT INTO BANK_CUSTOMER VALUES (123004,"Jack", "229-5, Concord",  	"CA" , "1897627999");
INSERT INTO BANK_CUSTOMER VALUES (123005,"Jacob", "325-7, Mission Dist","SFO", "1897637000");
INSERT INTO BANK_CUSTOMER VALUES (123006,"Noah", "275-9, saint-paul" ,  "MN" , "1897613200");
INSERT INTO BANK_CUSTOMER VALUES (123007,"Charlie","125-1,Richfield",   "MN" , "1897617666");
INSERT INTO BANK_CUSTOMER VALUES (123008,"Robin","3005-1,Heathrow", 	"NY" , "1897614000");

CREATE TABLE BANK_CUSTOMER_EXPORT ( customer_id CHAR(10),
customer_name CHAR(20),
Address CHAR(20),
state_code  CHAR(3) ,    	 
Telephone  CHAR(10));
    
INSERT INTO BANK_CUSTOMER_EXPORT VALUES ("123001 ","Oliver", "225-5, Emeryville", "CA" , "1897614500") ;
INSERT INTO BANK_CUSTOMER_EXPORT VALUES ("123002 ","George", "194-6,New brighton","MN" , "189761700");
CREATE TABLE Bank_Account_Details(Customer_id INT,           	 
                             	Account_Number VARCHAR(19),
                              	Account_type VARCHAR(25),
                           	    Balance_amount INT,
                               	Account_status VARCHAR(10),             	 
                               	Relationship_type varchar(1) ) ;
INSERT INTO Bank_Account_Details  VALUES (123001, "4000-1956-3456",  "SAVINGS" , 200000 ,"ACTIVE","P");
INSERT INTO Bank_Account_Details  VALUES (123001, "5000-1700-3456", "RECURRING DEPOSITS" ,9400000 ,"ACTIVE","S");  
INSERT INTO Bank_Account_Details  VALUES (123002, "4000-1956-2001",  "SAVINGS", 400000 ,"ACTIVE","P");
INSERT INTO Bank_Account_Details  VALUES (123002, "5000-1700-5001",  "RECURRING DEPOSITS" ,7500000 ,"ACTIVE","S");
INSERT INTO Bank_Account_Details  VALUES (123003, "4000-1956-2900",  "SAVINGS" ,750000,"INACTIVE","P");
INSERT INTO Bank_Account_Details  VALUES (123004, "5000-1700-6091", "RECURRING DEPOSITS" ,7500000 ,"ACTIVE","S");
INSERT INTO Bank_Account_Details  VALUES (123004, "4000-1956-3401",  "SAVINGS" , 655000 ,"ACTIVE","P");
INSERT INTO Bank_Account_Details  VALUES (123005, "4000-1956-5102",  "SAVINGS" , 300000 ,"ACTIVE","P");
INSERT INTO Bank_Account_Details  VALUES (123006, "4000-1956-5698",  "SAVINGS" , 455000 ,"ACTIVE" ,"P");
INSERT INTO Bank_Account_Details  VALUES (123007, "5000-1700-9800",  "SAVINGS" , 355000 ,"ACTIVE" ,"P");
INSERT INTO Bank_Account_Details  VALUES (123007, "4000-1956-9977",  "RECURRING DEPOSITS" , 7025000,"ACTIVE" ,"S");
INSERT INTO Bank_Account_Details  VALUES (123007, "9000-1700-7777-4321",  "Credit Card"	,0  ,"INACTIVE", "P");
INSERT INTO Bank_Account_Details  VALUES (123007, '5900-1900-9877-5543', "Add-on Credit Card" ,   0   ,"ACTIVE", "S");
INSERT INTO Bank_Account_Details  VALUES (123008, "5000-1700-7755",  "SAVINGS"   	,0   	,"INACTIVE","P");
INSERT INTO Bank_Account_Details  VALUES (123006, '5800-1700-9800-7755', "Credit Card"   ,0   	,"ACTIVE", "P");
INSERT INTO Bank_Account_Details  VALUES (123006, '5890-1970-7706-8912', "Add-on Credit Card"   ,0   	,"ACTIVE", "S");

# CREATE Bank_Account Table:
# Create Table
CREATE TABLE BANK_ACCOUNT ( Customer_id INT, 		   			  
	                Account_Number VARCHAR(19),
		     Account_type VARCHAR(25),
		     Balance_amount INT ,
			Account_status VARCHAR(10), Relation_ship varchar(1) ) ;
# Insert information:
INSERT INTO BANK_ACCOUNT  VALUES (123001, "4000-1956-3456",  "SAVINGS"            , 200000 ,"ACTIVE","P"); 
INSERT INTO BANK_ACCOUNT  VALUES (123001, "5000-1700-3456",  "RECURRING DEPOSITS" ,9400000 ,"ACTIVE","S");  
INSERT INTO BANK_ACCOUNT  VALUES (123002, "4000-1956-2001",  "SAVINGS"            , 400000 ,"ACTIVE","P"); 
INSERT INTO BANK_ACCOUNT  VALUES (123002, "5000-1700-5001",  "RECURRING DEPOSITS" ,7500000 ,"ACTIVE","S"); 
INSERT INTO BANK_ACCOUNT  VALUES (123003, "4000-1956-2900",  "SAVINGS"            ,750000,"INACTIVE","P"); 
INSERT INTO BANK_ACCOUNT  VALUES (123004, "5000-1700-6091",  "RECURRING DEPOSITS" ,7500000 ,"ACTIVE","S"); 
INSERT INTO BANK_ACCOUNT  VALUES (123004, "4000-1956-3401",  "SAVINGS"            , 655000 ,"ACTIVE","P"); 
INSERT INTO BANK_ACCOUNT  VALUES (123005, "4000-1956-5102",  "SAVINGS"            , 300000 ,"ACTIVE","P"); 
INSERT INTO BANK_ACCOUNT  VALUES (123006, "4000-1956-5698",  "SAVINGS"            , 455000 ,"ACTIVE" ,"P"); 
INSERT INTO BANK_ACCOUNT  VALUES (123007, "5000-1700-9800",  "SAVINGS"            , 355000 ,"ACTIVE" ,"P"); 
INSERT INTO BANK_ACCOUNT  VALUES (123007, "4000-1956-9977",  "RECURRING DEPOSITS" , 7025000,"ACTIVE" ,"S"); 
INSERT INTO BANK_ACCOUNT  VALUES (123007, "9000-1700-7777-4321",  "CREDITCARD"    ,0      ,"INACTIVE","P"); 
INSERT INTO BANK_ACCOUNT  VALUES (123008, "5000-1700-7755",  "SAVINGS"            ,NULL   ,"INACTIVE","P"); 




# CREATE TABLE Bank_Account_Relationship_Details

CREATE TABLE Bank_Account_Relationship_Details
                             	( Customer_id INT,
								Account_Number VARCHAR(19),
                            	Account_type VARCHAR(25),
                             	Linking_Account_Number VARCHAR(19));
INSERT INTO Bank_Account_Relationship_Details  VALUES (123001, "4000-1956-3456",  "SAVINGS" , "");
INSERT INTO Bank_Account_Relationship_Details  VALUES (123001, "5000-1700-3456",  "RECURRING DEPOSITS" , "4000-1956-3456");  
INSERT INTO Bank_Account_Relationship_Details  VALUES (123002, "4000-1956-2001",  "SAVINGS" , "" );
INSERT INTO Bank_Account_Relationship_Details  VALUES (123002, "5000-1700-5001",  "RECURRING DEPOSITS" , "4000-1956-2001" );
INSERT INTO Bank_Account_Relationship_Details  VALUES (123003, "4000-1956-2900",  "SAVINGS" , "" );
INSERT INTO Bank_Account_Relationship_Details  VALUES (123004, "5000-1700-6091",  "RECURRING DEPOSITS" , "4000-1956-2900" );
INSERT INTO Bank_Account_Relationship_Details  VALUES (123004, "5000-1700-7791",  "RECURRING DEPOSITS" , "4000-1956-2900" );
INSERT INTO Bank_Account_Relationship_Details  VALUES (123007, "5000-1700-9800",  "SAVINGS" , "" );
INSERT INTO Bank_Account_Relationship_Details  VALUES (123007, "4000-1956-9977",  "RECURRING DEPOSITS" , "5000-1700-9800" );
INSERT INTO Bank_Account_Relationship_Details  VALUES (NULL, "9000-1700-7777-4321",  "Credit Card" , "5000-1700-9800" );
INSERT INTO Bank_Account_Relationship_Details  VALUES (NULL, '5900-1900-9877-5543', 'Add-on Credit Card', '9000-1700-7777-4321' );
INSERT INTO Bank_Account_Relationship_Details  VALUES (NULL, '5800-1700-9800-7755', 'Credit Card', '4000-1956-5698' );
INSERT INTO Bank_Account_Relationship_Details  VALUES (NULL, '5890-1970-7706-8912', 'Add-on Credit Card', '5800-1700-9800-7755' );



# CREATE TABLE BANK_ACCOUNT_TRANSACTION

CREATE TABLE BANK_ACCOUNT_TRANSACTION (  
              	Account_Number VARCHAR(19),
              	Transaction_amount Decimal(18,2) ,
              	Transcation_channel VARCHAR(18) ,
             	Province varchar(3) ,
             	Transaction_Date Date) ;


INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-3456",  -2000, "ATM withdrawl" , "CA", "2020-01-13");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001",  -4000, "POS-Walmart"   , "MN", "2020-02-14");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001",  -1600, "UPI switch"  , "MN", "2020-01-19");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001",  -6000, "Bankers cheque", "CA", "2020-03-23");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001",  -3000, "Net banking"   , "CA", "2020-04-24");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001",  23000, "cheque deposit", "MN", "2020-03-15");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "5000-1700-6091",  40000, "ECS switch"  , "NY", "2020-02-19");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "5000-1700-7791",  40000, "ECS switch"  , "NY", "2020-02-19");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-3401",   8000, "Cash Deposit"  , "NY", "2020-01-19");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-5102",  -6500, "ATM withdrawal" , "NY", "2020-03-14");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-5698",  -9000, "Cash Deposit"  , "NY", "2020-03-27");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-9977",  50000, "ECS switch"  , "NY", "2020-01-16");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "9000-1700-7777-4321",  -5000, "POS-Walmart", "NY", "2020-02-17");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "9000-1700-7777-4321",  -8000, "Shopping Cart", "MN", "2020-03-13");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "9000-1700-7777-4321",  -2500, "Shopping Cart", "MN", "2020-04-21");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "5800-1700-9800-7755", -9000, "POS-Walmart","MN", "2020-04-13");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( '5890-1970-7706-8912', -11000, "Shopping Cart" , "NY" , "2020-03-12") ;



# CREATE TABLE BANK_CUSTOMER_MESSAGES

CREATE TABLE BANK_CUSTOMER_MESSAGES (  
              	Event VARCHAR(24),
              	Customer_message VARCHAR(75),
              	Notice_delivery_mode VARCHAR(15)) ;


INSERT INTO BANK_CUSTOMER_MESSAGES VALUES ( "Adhoc", "All Banks are closed resulting from announcement of National strike", "cellular" ) ;
INSERT INTO BANK_CUSTOMER_MESSAGES VALUES ( "Transaction Limit", "Only restricted withdrawals per card are allowed from ATM machines", "cellular" );
INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES
('4000-1956-9977' ,    10000.00     ,'ECS switch',     'MN' ,    '2020-02-16' ) ;

-- inserted for queries after seventeenth  
INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES
('4000-1956-9977' ,    40000.00     ,'ECS switch',     'MN' ,    '2020-03-18' ) ;

INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES
('4000-1956-9977' ,    60000.00     ,'ECS switch',     'MN' ,    '2020-04-18' ) ;

INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES
('4000-1956-9977' ,    20000.00     ,'ECS switch',     'MN' ,    '2020-03-20' ) ;

-- inserted for queries after twenty fourth 

INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES
('4000-1956-9977' ,    49000.00     ,'ECS switch',     'MN' ,    '2020-06-18' ) ;




# CREATE TABLE BANK_INTEREST_RATE

CREATE TABLE BANK_INTEREST_RATE(  
            	account_type varchar(24),
              	interest_rate decimal(4,2),
            	month varchar(2),
            	12 months  varchar(4)
             	)	;

INSERT  INTO BANK_INTEREST_RATE VALUES ( "SAVINGS" , 0.04 , '02' , '2020' );
INSERT  INTO BANK_INTEREST_RATE VALUES ( "RECURRING DEPOSITS" , 0.07, '02' , '2020' );
INSERT  INTO BANK_INTEREST_RATE VALUES   ( "PRIVILEGED_INTEREST_RATE" , 0.08 , '02' , '2020' );


# Bank_holidays:

Insert into bank_holidays values( '2020-05-20', now(), now() ) ;

Insert into bank_holidays values( '2020-03-13' , now(), now() ) ;


Print buyer Id, buyer identify and common account_balance maintained by every buyer for all of his/her accounts within the financial institution.

Select bc.customer_id , customer_name, avg(ba.Balance_amount) as All_account_balance_amount
from bank_customer bc
inside be a part of
Bank_Account_Details ba
on bc.customer_id = ba.Customer_id
group by bc.customer_id, bc.customer_name;

Print customer_id , account_number and balance_amount , 

#situation that if balance_amount is nil then assign transaction_amount  for account_type = “Credit Card”

Select customer_id , ba.account_number,
Case when ifnull(balance_amount,0) = 0 then   Transaction_amount else balance_amount finish  as balance_amount
from Bank_Account_Details ba  
inside be a part of
bank_account_transaction bat
on ba.account_number = bat.account_number
and account_type = "Credit Card";


Print customer_id , account_number and balance_amount , 

# conPrint account quantity,  balance_amount, transaction_amount from Bank_Account_Details and bank_account_transaction 

# for all of the transactions occurred throughout march,2020 and april, 2020

Select
ba.Account_Number, Balance_amount, Transaction_amount, Transaction_Date
from Bank_Account_Details ba  
inside be a part of
bank_account_transaction bat
on ba.account_number = bat.account_number
And ( Transaction_Date between "2020-03-01" and "2020-04-30");
-- or use under situation --  
# (date_format(Transaction_Date , '%Y-%m')  between "2020-03" and "2020-04"); 

Print all the buyer id, account quantity,  balance_amount, transaction_amount from bank_customer, 

# Bank_Account_Details and bank_account_transaction tables the place excluding all of their transactions in march, 2020  month 

Select
ba.Customer_id,
ba.Account_Number, Balance_amount, Transaction_amount, Transaction_Date
from Bank_Account_Details ba  
Left be a part of bank_account_transaction bat
on ba.account_number = bat.account_number
And NOT ( date_format(Transaction_Date , '%Y-%m') = "2020-03" );

Print solely the client id, buyer identify, account_number, balance_amount who did transactions throughout the first quarter. 

# Do not show the accounts in the event that they haven’t executed any transactions within the first quarter.

Select
ba.Customer_id,
ba.Account_Number, Balance_amount , transaction_amount , transaction_date from
Bank_Account_Details ba  
Inner be a part of bank_account_transaction bat
on ba.account_number = bat.account_number
And ( date_format(Transaction_Date , '%Y-%m') <= "2020-03" );

Print account_number, Event adn Customer_message from BANK_CUSTOMER_MESSAGES and Bank_Account_Details to show an “Adhoc” 

# Event for all clients who’ve  “SAVINGS” account_type account.

SELECT Account_Number, Event , Customer_message 
FROM Bank_Account_Details 
CROSS JOIN 
BANK_CUSTOMER_MESSAGES 
ON Event  = "Adhoc"  And ACCOUNT_TYPE = "SAVINGS";

Print Customer_id, Account_Number, Account_type, and show deducted balance_amount by  

# subtracting solely adverse transaction_amounts for Relationship_type = “P” ( P – means  Primary , S – means Secondary )

SELECT
	ba.Customer_id,
	ba.Account_Number,    
	(Balance_amount + IFNULL(transaction_amount, 0)) deducted_balance_amount
 
FROM Bank_Account_Details ba
LEFT JOIN bank_account_transaction bat 
ON ba.account_number = bat.account_number 
AND Relationship_type = "P";


Display information of All Accounts, their Account_types, the transaction quantity.

# b) Along with step one, Display different columns with the corresponding linking account quantity, account varieties 

SELECT  br1.Account_Number primary_account ,
    	br1.Account_type primary_account_type,
    	br2.Account_Number Seconday_account,
    	br2.Account_type Seconday_account_type
FROM `bank_account_relationship_details` br1  
LEFT JOIN `bank_account_relationship_details` br2
ON br1.account_number = br2.linking_account_number;

Display information of All Accounts, their Account_types, the transaction quantity.

# b) Along with step one, Display different columns with corresponding linking account quantity, account varieties 

# c) After retrieving all information of accounts and their linked accounts, show the transaction quantity of accounts appeared in one other column.

SELECT br1.Account_Number primary_account_number ,
br1.Account_type      	 primary_account_type,
br2.Account_Number    	secondary_account_number,
br2.Account_type      	secondary_account_type,  
bt1.Transaction_amount   primary_acct_tran_amount
from bank_account_relationship_details br1
LEFT JOIN bank_account_relationship_details br2
on br1.Account_Number = br2.Linking_Account_Number
LEFT JOIN bank_account_transaction bt1
on br1.Account_Number  = bt1.Account_Number;


Display all saving account holders have “Add-on Credit Cards” and “Credit cards” 

SELECT  
br1.Account_Number  primary_account_number ,
br1.Account_type  primary_account_type,
br2.Account_Number secondary_account_number,
br2.Account_type secondary_account_type
from bank_account_relationship_details br1
JOIN bank_account_relationship_details br2
on br1.Account_Number = br2.Linking_Account_Number
and br2.Account_type like '%Credit%' ;

That covers probably the most requested or SQL practiced questions.

Frequently Asked Questions in SQL

1. How do I put together for the SQL interview?

Many on-line sources may also help you put together for an SQL interview. You can undergo transient tutorials and free on-line programs on SQL (eg.: SQL fundamentals on Great Learning Academy) to revise your information of SQL. You may also observe initiatives that can assist you with sensible elements of the language. Lastly, many blogs comparable to this record all of the possible questions that an interviewer would possibly ask. 

2. What are the 5 primary SQL instructions?

The 5 primary SQL instructions are:

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)
  • Transaction Control Language (TCL)
  • Data Query Language (DQL)

3. What are primary SQL abilities?

SQL is an unlimited matter and there’s a lot to study. But probably the most primary abilities that an SQL skilled ought to know are:

  • How to construction a database
  • Managing a database
  • Authoring SQL statements and clauses
  • Knowledge of fashionable database techniques comparable to MySQL
  • Working information of PHP
  • SQL information evaluation
  • Creating a database with WAMP and SQL

4. How can I observe SQL?

There are some platforms out there on-line that may show you how to observe SQL comparable to SQL Fiddle, SQLZOO, W3resource, Oracle LiveSQL, DB-Fiddle, Coding Groud, GitHub and others. Also take up a Oracle SQL to study extra.

5. Where can I observe SQL questions?

There are some platforms out there on-line that may show you how to observe SQL comparable to SQL Fiddle, SQLZOO, W3resource, Oracle LiveSQL, DB-Fiddle, Coding Groud, GitHub and others. 

You may also check with articles and blogs on-line that record crucial SQL interview questions for preparation.

6. What is the commonest SQL command?

Some of the commonest SQL instructions are:

  • CREATE DATABASE 
  • ALTER DATABASE
  • CREATE TABLE
  • ALTER TABLE 
  • DROP TABLE
  • CREATE INDEX
  • DROP INDEX

7. How are SQL instructions categorized?

SQL Commands are categorized underneath 4 classes, i.e.,

  • Data Definition Language (DDL)
  • Data Query Language (DQL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)

8. What are primary SQL instructions?

Basic SQL instructions are:

  • CREATE DATABASE 
  • ALTER DATABASE
  • CREATE TABLE
  • ALTER TABLE 
  • DROP TABLE
  • CREATE INDEX
  • DROP INDEX

9. Is SQL coding?

Yes, SQL is a coding language/ programming language that falls underneath the class of domain-specific programming language. It is used to entry relational databases comparable to MySQL.

10. What is SQL instance?

SQL helps you replace, delete, and request data from databases. Some of the examples of SQL are within the type of the next statements:

  • SELECT 
  • INSERT 
  • UPDATE
  • DELETE
  • CREATE DATABASE
  • ALTER DATABASE 

11. What is SQL code used for?

SQL code is used to entry and talk with a database. It helps in performing duties comparable to updating and retrieving information from the databases.

To Conclude

For anybody who’s well-versed in SQL is aware of that it’s the most generally used Database language. Thus, probably the most important half to study is SQL for Data Science to energy forward in your profession.

Wondering the place to study the extremely coveted in-demand abilities at no cost? Check out the programs on Great Learning Academy. Enroll in any course, study the in-demand talent, and get your free certificates. Hurry!

Free Resources

LEAVE A REPLY

Please enter your comment!
Please enter your name here