SQL Bootcamp with SQL Video with 30 SQL question answers

SQL Bootcamp with SQL Video and 30 SQL question answers

Hello Guys,

This post brings you a lot off knowledge about SQL. Which contains the basic information about SQL  for beginners, information about SQL Bootcamp, SQL  video course information and contents, and 30 SQL question and answers those will increase your knowledge about SQL a quite well!

So, lets start the journey! 

What is SQL?

The programming language used by a database management system (DBMS) for maintaining and querying data stored in a relational database is called SQL, or Structured Query Language.

Since 1986, SQL has been an ANSI standard, and every DBMS has its own version of the standard. 

Since these various implementations adhere to the standard in differing degrees, in reality, SQL code of any complexity is rarely transferable between various DBMS systems without considerable modification. 

However, due to the standard, knowledge gained in one DBMS system may be used in most other DBMS systems.

SQL Implemented Different Databases

1.SQLite

2.PostgreSQL

3.SQL Server

4.MySQL

let's see the Examples of SQL code

Selecting first and last name from a table of users:

SELECT firstName, lastName

FROM users;

------------------------------------------------

Delete records from a transaction table with a particular field value:

DELETE FROM transactionTable

WHERE transactionKey = '5';

-------------------------------------------------

Create a table with three fields:

CREATE TABLE cars (

  manufacturer VARCHAR(40),

  model VARCHAR(40),

  year INT

);

--------------------------------------------------

I'm sure you want to learn all and all about SQL. i will suggest you about a SQL boot camp course here and will give you all info about it. The course is from world famous Udemy.

Learn how to use SQL quickly and effectively with this course!

SQL is one of the most in demand skills for business analysts, data scientists, and anyone who finds themselves working with data!

In this SQL Bootcamp, You'll learn how to read and write complex queries to a database using one of the most in demand skills - PostgreSQL. 

These skills are also applicable to any other major SQL database, such as MySQL, Microsoft SQL Server, Amazon Redshift, Oracle, and much more.

Learning SQL is one of the fastest ways to improve your career prospects as it is one of the most in demand tech skills! In this course you'll learn quickly and receive challenges and tests along the way to improve your understanding!

In this course you will learn everything you need to become a SQL Pro! Including:

• Get started with PostgreSQL and PgAdmin , two of the world's most popular SQL tools

• Learn he basics of SQL syntax

• Analyzing data using aggregate functions with GROUP BY commands

• Running advanced queries with string operations and comparison operations

• Learn to use logical operators to add logic flow to your SQL queries

• Learn common SQL JOIN commands

• Learn to create tables and databases with constraints on data entries

• Learn to use Python to further advanced your SQL skills

• and much, much more!

This is course that put's you in control, having you set up and restore databases right at the start of the course, instead of watching someone else code. 

Every section comes with fresh challenge questions and tasks, modeled after real world tasks and situations.

 The instructor has spent years as both online and in-person at Fortune 500 companies, and this course is built to combine the best of both worlds, allowing you to learn at your own pace through an interactive environment. 

You will start with the basics and soon find yourself working with advanced commands, dealing with timestamp data and variable character information like a seasoned professional.

-------------------------------------------------------------------------------------------------------

Now, lets see about the SQL Video learning Live Lessons.

 first let me tell you, What are Live lessons videos.

LiveLessons Video Training series publishes hundreds of hands-on, expert-led video tutorials covering a wide selection of technology topics designed to teach you the skills you need to succeed. 

This professional and personal technology video series features world-leading author instructors published by your trusted technology brands. 

That Include, Addison-Wesley, Cisco Press, IBM Press, Pearson IT Certification, Sams, and Que. Topics include IT Certification, Programming, Web Development, Mobile Development, Home and Office Technologies, Business and Management, and more.

The fundamental skills you need to succeed with SQL are taught by best-selling author Ben Forta in his Learning SQL LiveLessons course.

This video Live Lessons are designed by Reputed and renown Addison-Wesley Professional and Almost three hours of video training.

 Here, in this Live lessons video  training, Learn the fundamentals of SQL quickly and easily from the world's favorite SQL teacher Ben Forta. 

Drawing on his experience helping thousands of developers and other IT pros at all levels, Forta guides you, one-on-one, from the absolute basics all the way to queries, joins, and beyond.

you'll learn how to retrieve data with SELECT; sort, filter, group, and summarize data; create calculated fields; manipulate data with string and date functions; build queries and subqueries; create basic and advanced table joins; combine queries with UNION; sort your queries; insert, update, and delete data,

and even discover what you can do with triggers and stored procedures.

Lets see whats inside the SQL Live lessons video training.

Lesson 1: Introducing SQL

Review database basics such as tables, rows, columns, and keys; understand what relational databases are; choose a DBMS; and learn what SQL is and what it does.

Lesson 2: Retrieving Data

Work with SELECT, the most used SQL statement. Retrieve individual, multiple, or all columns; retrieve specific rows; and limit the data you retrieve. Master and refine some important SQL skills and best practices.

Lesson 3: Sorting Retrieved Data

Use SELECT ORDER BY, sort by one or multiple columns, and specify the direction of your sort.

Lesson 4: Filtering Data

Use SELECT WHERE and its clause operators, use BETWEEN to filter for a range of values, and use NULL to filter for no value.

Lesson 5: Advanced Data Filtering

Combine WHERE filters using AND and OR; correctly manage order of evaluation; use IN to filter by a defined set of matches; and negate clauses with NOT.

Lesson 6: Using Wildcard Filtering

Create wildcard filters with the LIKE keyword, and learn three wildcards you can use to search for any character combinations.

Lesson 7: Creating Calculated Fields

Use concatenation and aliases, and perform on-the-fly mathematical calculations using calculate fields and mathematical operators.

Lesson 8: Using Data Manipulation Functions

Manipulate data with string and date functions.

Lesson 9: Summarizing Data

Use the aggregate functions AVG, COUNT, MAX, MIN, and SUM to summarize data, totals, averages, and more.

Lesson 10: Grouping Data

Group data with the GROUP BY clause, and use the HAVING clause to filter by group rather than row.

Lesson 11: Working with Subqueries

Create powerful subqueries (queries passed to other queries) to filter rows in conjunction with WHERE and to return values to calculated fields.

Lesson 12: Joining Tables

Master and compare two ways to create simple joins with keys. Then, learn how to join multiple tables.

Lesson 13: Creating Advanced Joins

Use table aliases, self-joins, and outer joins, and combine joins with aggregate functions.

Lesson 14: Combining Queries

Use UNION to combine query result sets from multiple SELECT statements, include or exclude duplicate rows, and use ORDER BY to sort the queries youve combined.

Lesson 15: Inserting Data

Insert rows or retrieved data, avoid unsafe ways to use the INSERT statement, master best practices to prevent accidental data changes or deletion, and copy data to another table.

Lesson 16: Updating and Deleting Data

Update and delete table rows. Master UPDATE and DELETE best practices to make sure you dont inadvertently change or delete data that should remain intact.

Lesson 17: Where to Go from Here

Discover what can be done with advanced features such as views, stored procedures, triggers, constraints, and transactions.

What You Will Learn more from SQL Live Lessons training video course ?

• Retrieve data using the SQL SELECT statement
• Sort, filter, and manipulate data
• Create calculated fields
• Group and summarize data
• Work with subqueries and WHERE statements
• Use basic and advanced table joins
• Combine queries with UNION statements
• Insert, update, and delete data
• Get started with advanced SQL features 

Who Should Take This SQL Video live lessons Course?

• Everyone who wants to master SQL to manage data, write queries, and/or build database applications with any modern relational database
• Professional and casual developers at all levels

---------------------------------------------------------------------------------------------------------

Now it's time to present the 30 question answers to gain SQL Knowledge

1. What Are The Steps To Take To Improve Performance Of A Poor Performing Query?

 

 Maximum use of indexes, stored procures should be done.

 Avoid excessive use of complicated joins and cursors.

 Avoid using conditional operators using columns of different tables.

 Make use of computed columns and rewriting the query.

2. What Is A Deadlock And What Is A Live Lock?

 

Deadlock occur in interconnection n/w when group of process are unable to act because of waiting each other to release some resource. 

live lock packets continue to move through n/w, but does not advance towards destination.

3. What Is Blocking And How Would You Troubleshoot It?

 

Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the first lock is released.

Troubleshooting blocking:

 SQL scripts can be written that constantly monitor the state of locking and blocking on SQL Server

 The common blocking scenarios must be identified and resolved.

 The scripts output must be checked constantly.

 The SQL profilers data must be examined regularly to detect blocking.

4. Explain The Different Types Of Backups Available In Sql Server.

 

Complete database backup: This type of backup will backup all the information in the database. Used most commonly for disaster recovery and takes the longest time to backup.

Differential databse backup: The database is divided into partitions that have been modified since last complete backup. Most suitable for large databases. The most recent differential backup contains the changes from previous backups.

Transaction log backups: Backups only the changes logged in the transaction log. The transaction log has all changes logged about a database. Once the changes are accommodated on the database, the log is truncated or backed up.

File/File Group backups: used to recover individual files or file groups. Each filegroup can be individually backed up. This helps in recovery only the required file or filegroup for disaster recovery.

5. What Is Database Isolation In Sql Server?

 

Isolation in database defines how and when changes made by one transaction can be visible to other transactions. Different isolation levels are:

 Serializable

 Repeatable read

 Read committed

 Read uncommitted

6. What Is A Schema In Sql Server 2005? Explain How To Create A New Schema In A Database?

 

A schema is used to create database objects. It can be created using CREATE SCHEMA statement. The objects created can be moved between schemas. Multiple database users can share a single default schema.

CREATE SCHEMA sample;

Table creation

Create table sample.sampleinfo

{

id int primary key,

name varchar(20)

}

7. Explain How To Create A Scrollable Cursor With The Scroll Option.

 

Using the SCROLL keyword while declaring a cursor allows fetching of rows in any sequence.

Example:
DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM employee;

The active set of the cursor is stored can be accessed in any order without the need of opening and closing the cursor. The Scroll cursors can be set for select and function cursors but not insert or update statements.

8. Explain How To Create A Dynamic Cursor With The Dynamic Option?

 

When a cursor is declared as DYNAMIC, the cursor reflects all changes made to the base tables as the cursor is scrolled around.

Declare cursor_name cursor

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

FOR select_statement

The dynamic option does not support ABSOLUTE FETCH.

9. What Are Database Files And Filegroups?

Database files are used for mapping the database over some operating system files. Data and log information are separate. SQL server database has three types of database files:

Primary: starting point of a database. It also points to other files in database.
Extension: .mdf

Secondary: All data files except primary data file is a part of secondary files.
Extension: .ndf

Log files: All log information used to recover database. 
Extension: .ldf

10. Describe In Brief Databases And Sql Server Databases Architecture.

 

SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. Database architecture: All the data is stored in databases which is organized into logical components visible to the end users. It’s only the administrator who needs to actually deal with the physical storage aspect of the databases, whereas users only deal with database tables.

Every SQL Server instance has primarily 4 system database i.e. master, model, tempdb and msdb. All other databases are user created databases as per their needs and requirements.

A single SQL Server instance is capable of handling thousands of users working on multiple databases.

11. What Are The Steps To Improve The Performance Of A Query?

 

● Number of joins and use of complex views/cursors have to be reduced.
● The use of the stored procedures and indexes have to be maximized.

● The optimized use of the complex conditional checks and computer columns have to be in place.
● Tracking of performance analysis for the query helps us in identifying the right aspects to optimize.

12. How Would You Use The Sp_ Functions To Identify The Blocking Problems?

 

Blocking is the deadlock situation when two SQL connections race to obtain the control over the same set of rows in conflicting terms. This can be tracked by the status of WAIT present in the SP_LOCK procedure’s output. All the active LOCKS and the different rows that are being involved are shown in this output. The identification of the connections involved in the specific row contention lock can be identified with sp_who and sp_who2 procedures. This way the causal agents of the blocking is identifies. KILL command issued against the specific SQL connection causing the BLOCK can resolve the issue. But the permanent solution lies in the proper design of the application code to execute in concurrence across different connections.

13. What Are The Different Types Of Backups?

 

The SQL server offers 4 types of backups to suit the need of the administrator.

● Complete backup- The complete back up is just zipping the content of the entire database in terms of the different tables and procedures etc. This back up can server as an independent entity that can be restored in different systems with just the base SQL server installed.
● Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in the server. This way the details of the database getting updated is obtained. This cannot be a stand-alone back up mechanism. But can save a lot of time if we already have the file system related to the DB backed up on the new deployment server.
● Differential backup: This is a subset of the complete backup, where only the modified datasets are backed up. This can save the time when we are just trying to maintain a backup server to main server.

● File backup: This is the quickest way to take the backup of entire database. Instead of taking in the data actually stored in DB, the files are backed up and the file system thus obtained when combined with the transaction logs of the original system will render the database that we are trying to back up.

14. What Are The Different Levels Of Isolation?

 

The isolation represents the way of separating the database from the effects of network accesses, thereby maintaining the consistency. The different levels of isolation are:

● read committed: This level of isolation uses the shared locks and the reads to the database give the constant and consistent values.
● read uncommitted: No locks implemented. This is the least effective isolation level.
● repeatable read: There are locks over the rows and values but the updates are maintained as a separate phantom row which is the next set of values for the specific record. Values can change within a specific transaction of a SQL function. 
● SERIALIZABLE reads: This is the implementation of pure lock mechanism where one specific transaction is not allowed access to specific record before another one completes.

15. How Can You Start The Sql Server In The Single User Mode And The Minimal Configuration Mode?

 

The SQLServer.exe is the executable which can be called in the command prompt with the parameters -m and -f. These are the options that will start the SQL server in the user mode and minimal configuration mode respectively.

16. How Can You Know That Statistics Should Be Updated?

 

Statistics represent the uniqueness for the indexes that are being used for selecting the records. This can make the query execution pretty efficient. The tables that we are dealing with if truncated and repopulated, there is a good chance that the indexes and statistics are out of sync and this is when we have to update the statistics. There are also other situations like when the table has been modified and lot of rows have been added recently or like when a server has been updated with different version of software. These also give us the reason to use the UPDATE_STATISTICS, DBCC SHOW_STATISTICS etc to update it accordingly.

17. What Is Replication In Sql Server?

 

Replication refers to the moving or copying of the database elements from one system to another. This can be done in the SQL Server in one of the following methods:
● Transactional.
● Snapshot.
● Merge replication.

18. Can We Initiate A External Com Object From Within Sql?

 

Yes we can use the stored procedure sp_OACreate to initiate the external COM object from the T-SQL.

19. What Is A Schema? How Is It Useful In Sql Servers?

 

The Schema refers to the overall structure of the database with all related information like users, access privileges, interaction information between the different tables etc. The CREATE SCHEMA is the command that can be used to create the schema in the SQL Server. This when done can be used to re deploy the same database in another system for demonstrative or test purposes. This holds intact the underlying framework over which the database has been built.

20. What Is A Write-ahead Log?

 

The write-ahead log is the logging system that just updates the buffer cache of the database for the transactions and updates the logs and only then the actual changes are incorporated in the actual database. This is the reason why it is called “write ahead”. This helps in maintaining the consistency in the database. This can also be useful in getting the actual database values even in case of failures.

21. What Is The Use Of Check Points In The Transaction Logs?

 

The check points are restoration points that indicate the specific state of the database. When there is some failure in the database that is occurring before the next check point, the database can be reverted back to the previous check point and thus the database would still be consistent.

22. What Is A Column With Identity?

 

The column with a defined identity in turn means that there is an unique value that the system assigns to the specific column. This is similar to the AUTONumber property of the Access backend.

23. What Are Scrollable Cursors? How Are They Created?

 

The scrollable cursors are the ones that can get the entire set of rows as single entity, within which all the rows present can be accessed in any order without the open/close of cursor done for every row access. The scrollable cursors are created with the keyword SCROLL added to the CREATE Cursor statements. The scrollable cursors are useful for the access of information from different rows but not for the delete/insert of new rows. 

24. What Is Raid? How Does It Help Storage Of Databases?

 

The RAID stands for Redundant Array of Independent Disks. With its own RAID controllers, the RAID implements a fail-safe storage mechanism with its own backup mechanisms. There are different configurations of the RAID that all give us the ACID properties of storage along with other such facilities. This kind of storage will make the SQL Server database to be failsafe and stable. This can sometimes mean that the backup mechanisms and other such reliability measures can be taken off from the SQL Server level of operations.

25. How Can You Identify The Version Number Of The Sql Server Installed?

 

The global variable version has the build and version information for the SQL Server and the service packs.

26. What Is The Use Of Cascade Constraints?

 

cascading is used for maintaining referencial integrity rules, which says that foreign key attributes values should be either subset of primary key values or null.

27. What Is The Function Of A Odbc Manager ?

 

The ODBC Manager manages all the data sources that exists in the system.

28. What Are The Different Types Of Indexes Available In Sql Server?

 

“Clustered and Non-Clustered Indexes”. There are other types of Indexes such as Unique, XML, Spatial and Filtered Indexes. 

29. What Is The Difference Between Clustered And Non-clustered Index?

 

 In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.

 In a Non-Clustered index, the leaf level pages does not contain data pages instread it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

30. What Are The New Features In Sql Server 2005 When Compared To Sql Server 2000?

 

There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here :

 Database Partitioning

 Dynamic Management Views

 System Catalog Views

 Resource Database

 Database Snapshots

 SQL Server Integration Services

 Support for Analysis Services on a a Failover Cluster. ...

============================================