SQL (Questions)

Have you heard about the option STRICT ON in your query?

Turns on error and warning checking for, implicit narrowing conversions, late binding, and implicit typing that results in an Object type.

What are joins?

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

How do you use indexes in a database?

CREATE INDEX index_name ON table_name (column_name) To use it you just query the database using WHERE on the column name. To drop the index use Drop Index 'indexName'

What are four basic commands for dealing with data in SQL?

CRUD (CREATE, READ, UPDATE, and DELETE)

List and explain a few classes in SQL

Classes? Assuming the question means clauses, SELECT chooses what data, FROM designates what table, and WHERE specifies what conditions for a query.

How to pass a parameter into an SQL Stored procedure

EXEC procedureName @parameterName = value

What is the SQL feature that links tables?

Foreign Keys link tables together in one-to-one, one to many, and many to many relationships. Foreign keys make one column in two separate tables linked, so that the row related to one table can be called from the linked table and vice versa.

What is your experience with SQL?

I know how to query the database, create and alter tables, and manipulate data (CRUD)

Describe your SQL experiences with SQLServer and Oracle.

I've worked closely with DBA in past projects using SQL Server Management Studio. I'm familiar with how to create/edit tables, foreign keys, unique identifiers, clustered index.

What would happen if you put your select statements directly in your C# code?

If you put your select statement in the C# code, you risk the chance of getting SQL Injection Attack.

Have you used temporary table variables?

No

Have you used SQL Profiler? What is it for?

No. A SQL Profiler is a tool designed to help system administrators monitor events, stored procedures, transactions, queries, etc. that occur within a server.

How would you implement SQL server databases?

Open up Sql Server Management Studio. Connect to your database server. Right click databases folder, click New.

How would you improve performance of the database in SQL Server?

Optimize queries. Use stored procedures. Remove unnecessary indexes. Monitor your data access performance.

What is star schema?

Star schema is a design pattern used on data warehouses. Star schemas are denormalized. One benefit is increased query performance. The schema typically involve dimensions which revolve around a fact table.

What are primary keys?

The PRIMARY KEY constraint uniquely identifies each record in a database table.

What is a Cross Join or Cartesian Product?

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product. If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

What are Transactions?

These are the unit of work performed against the database. Now, this work is performed logically either by the user, or automatically by the database.

What are the two different types of union statements and what do they do?

Union combines 2 tables eliminating duplicates. Union all combines 2 tables without duplicates.

Have you ever used TSQL? PL/SQL?

Yes

Can you have more than one index on a table

Yes. It's not recommended unless those columns will be frequently queried. There can be many non-clustered indexes but only one clustered index.

What is a Right Outer Join (Right Join)?

A right outer join of A and B gives the matching results of A union B as well as all the non matching results of table B

What is a trigger?

A special kind of stored procedure that executes on an event in the database server.

What is the SQL function that returns the total number of all entries?

Count(Either a select statement or other function)

Are you comfortable with manipulating query in a database?

Yes.

How would you [set up the database tables to] handle a many-to-many data relationship?

You would set up an intermediate table (junction table), with the columns being two foreign keys, that point to the 2 primary keys in the tables you want to have the many to many relationship.

When would you use the outer join?

You would use an outer join when you want to get more than just the matching records of two table. You would use a left outer to also get all the records from the left table, a right outer for the opposite, and a full outer to return all records matching and not matching.

What is referential integrity in a database?

a DB has referential integrity if the foreign keys (the references) are actually pointing to an entry in a different table (the reference has integrity)

Have you written tables and created queries?

yes

What is the extension of a SQL Server Database Log file?

.ldf

What is the difference between a table-valued function and a scalar function?

A Table-Valued Function returns a result set like a view. Unlike a view it can accept parameters and have multiple lines of code. A Scalar Function is a traditional function. It accepts any number of parameters and returns one value.

What are foreign keys?

A column in a table that references a column( most often the primary key) in another table

What is a Cursor?

A cursor is a pointer to a row that iterates a table. Use them when you want to process a table one row at a time. Cursors are generally slow.

What is an Inner Join?

A intersect B; the middle part of the venn diagram. only returns parts of A that join on the field in B AND the parts of B that join on the field in A.

What is a Left Outer Join (Left Join)?

A left outer join of A and B gives the matching results of A union B as well as the non matching results of table A

Name some Constraints of Foreign Keys.

-NOT NULL - Indicates that a column cannot store NULL value -CHECK - Ensures that the value in a column meets a specific condition -DEFAULT - Specifies a default value when specified none for this column

What is the extension of a SQL Server Database file?

.mdf

How would you setup an SQL database?

1. Open Microsoft SQL Management Studio. 2. Expand the Microsoft SQL Server node where you want to create the database. 3. Right click the Databases node and then click New Database. 4. Type the database name in the dialog box, for example, MailSecurityReports, and then click OK.

What are stored procedures used for? Advantages?

A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. Advantages: Maintainability -Because scripts are in one location, updates and tracking of dependencies based on schema changes becomes easier Testing -Can be tested independent of the application Speed / Optimization -Stored procedures are cached on the server Security -Provide an "interface" to the underlying data structure so that all implementation and even the data itself is shielded.

What is an Equi Join versus a Natural Join?

An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns. A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

What is an Outer Join?

An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union from A and B.

What are the four properties a Transaction must exhibit?

Atomicity (all modifications or none are performed) Consistency (data is left in consistent state) Isolation (locks data to prevent dirty reads) Durability (once committed changes are permanent)

Describe what database normalization is. What are the levels?

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy. Levels First normal form (1NF) The domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain. Second normal form (2NF) An entity type is in 2NF when it is in 1NF and when all of its non-key attributes are fully dependent on its primary key. Third normal form (3NF) An entity type is in 3NF when it is in 2NF and when all of its attributes are directly dependent on the primary key.

What is the difference between Delete and Truncate?

Delete -Used to delete Rows in a Table (with or without specified condition) -Able to rollback Truncate -Used to delete all the rows from the table and free the space containing the table. -Unable to rollback

How do you optimize queries?

In general, keep your DBs normalized Move queries to stored procedures:SPs are compiled and run on the server Limit your searches as much as possible-only grab what you need Don't use joins if you don't have to, they are expensive. Try to join on indexed fields if you can. Add indexes to fields you often filter by, especially if they are an Integer, Boolean, or Number You can limit the number of rows returned, or possibly do a pagination, so you don't have to get a giant amount of rows.

How would you rate your SQL skills?

Proficient , I can create queries easily but I know that there is a lot more I could learn.

Differentiate between Outer and Inner joins.

Inner -selects all rows from both tables as long as there is a match between the columns in both tables. Think of the overlapping portions of a Venn diagram Outer -Full: Selects from both tables -Left: returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match -Right: returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match

What is connection pooling?

Optimization technique that reduces the number of times a new connection must be opened . In brief, a 'pooler' object is created that maintains the actual connection (ports, authentication, etc) and the pooler manages the connections. conn.Open()/.Close() will actually go to the pooler, not the connection.

How do you return all rows in a table where the Primary Key first name starts with "a"

SELECT * FROM tablename WHERE primarykey LIKE "a%";

Which version of SQL have you used?

SQL Server, MySQL, TSQL

What is partitioning?

Table partitioning is a way to divide a large table into smaller, more manageable parts without having to create separate tables for each part. Data in a partitioned table is physically stored in groups of rows called partitions and each partition can be accessed and maintained separately

What is an alias?

SQL aliases are used to give a database table, or a column in a table, a temporary name. Aliases makes column name more readable

What are and have you ever used temporary tables in SQL Server?

Temp tables are actual tables which are stored in temp_db folder in the sql server. To create one you just add a hashtag to the name CREATE Table #table1 They are used to store intermediate results for immediate use. can be queried like regular tables. content is dropped when out of scope (?) but definition is kept until explicitly dropped.

What are Foreign Key Constraints?

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

Have you done defined functions in SQL?

These functions can be a Built-In or User-Defined. Now, User-Defined functions allows us to create our own functions using the CREATE FUNCTION statement. The Built-IN function operates in the Transact SQL Reference and CANNOT BE MODIFIED. They can only be referenced in the Transact SQL statement only by syntax from Transact SQL Reference.

How do you connect to the database in a ASP.NET application?

Use the System.Data.SqlClient namespace and use SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter. Or use an ADO.Net entity data model, which will create a connectionstring for you once you specify your connection properties.

Where would you use the WHERE and the HAVING clauses.

Use the Where clause towards the end of a query, after Select, From, or Join but before Order By and Having, to specify your search. The Having clause goes after a Where or Order By, for limiting rows using an aggregate function.

Why use a table-valued function instead of just a select?

User-defined functions that return a table data type are referred to as table-valued functions. A user-defined functions can contain additional SELECT statements that allow more powerful logic than is possible in views (a view is limited to a single SELECT statement).

Difference between a view and a materialized view.

Views are virtual; they are always up to date, and have slower performance. Materialized views are actually created; data is updated periodically, but they have quick performance.

What is the effect of indexing on SQL Tables?

Without it, a search would have to scan the whole table. Therefore Selecting is faster, Update and Insert are slower. Only index columns that are frequently searched should be indexed.