Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Answers to Steve Jones's SQL Quiz

By Steve Jones,

Answers To My SQL Questions

I provided some SQL questions for an interviewer in Who Do You Hire? to ask of a candidate. I had a couple requests for answers, so I decided to provide some. I would recommend you check the questions first before you read these.


  1. This is a cross product or a full outer join. The number of rows is 5 * 3 = 15
  2. A correlated subquery is a query within a query where the inner query is evaluated for each row in the outer query.
     select o.orderid
    	from orders o
     	where o.custid = ( select o1.custid
    					from orders o1
    					where o1.qty > 1
    					and o1.orderid = o.orderid
    				)
    
    
  3.  select distinct c.name, o.orderid
    	from customers c, orders o
    	where c.custid = o.custid
    
  4.  select c.name, o.orderid, count(o.orderid)
    	from customers c, orders o
    	where c.custid = o.custid
    	group by c.name, o.orderid
    	having count( o.orderid) > 1
    
  5. RI is explicitly declaring the relationships between tables (foreign keys) using DDL and the built-in server functions rather than using triggers or stored procedures to enforce relationships.
  6. Normalization is a technique for storing data in a relational database system. There are various levels or normalization, but when most people speak of Normalization, they are referring to third normal form. Under this level, there are no repeating data elements, fields depend on the complete primary key, and no dependencies on non-key columns. Also no derived columns.

    Usually third normal form reduces the amount of data storage needed as well as makes OLTP applications quicker by reducing the amount of data necessary for an insert.

    Denormalization is often done to reduce the number of joins required for a query. Usually in OLAP applications.

  7. I would perform nightly full backups, differential backups every four hours between fulls, and transaction log backups every hour in between. If additional protection against loss of data was needed, this schedule could be changed to reduce the time between transaction log backups. I would perform all backups to a local disk using native SQL dump. Then these would be copied to a remote server and backed up to tape from that server.
  8. There are two ways to do this. If the event is tracked by SQL Server, an alert can be set to notify someone with email / pager / broadcast. If this is a business alert, then code would have to be written using a stored procedure to manually send the alert.
  9. Have a stored procedure that checked for large sales, by whatever definition is being used for large. This procedure would send an email alert to the sales manager. I would schedule a task using SQL Agent / SQL Executive to run this task as often as needed.
  10. Online Analytical Processing. Usually used in to describe the client access to a "cube" of precalculated data that is designed for reporting purposes. The data is often stored in a star schema that allows for rapid access and manipulation of data based on dimensions and calculated measures the cube designer has incorporated into the cube.
  11. Given a basic requirement without knowing additional details, here is what I would propose:
    Setup the following partitions:
    • RAID 1 partition for the OS and SQL OS
    • RAID 1 partition for the pagefile
    • RAID 1 partition for each log file
    • RAID 5 partition for each data file group - heavily used tables could be placed on their own RAID 5 partion and filegroup
    I would 2 dual processor machines in a cluster configuration with 2GB of RAM each.
  12. Clustered indexes are indexes where the data rows are actually the leaf nodes of the index. The data is then physically stored in the indexed order.
    Only one clustered indexes per table
    255 additional nonclustered indexes per table.
  13. In query analyzer, view the execution plan and look for scans rather than seeks. Perhaps additional indexes would help. Update the statistics if there are indexes on these tables. I would also ensure that the tables are not fragmented by checking dbcc showcontig and perhaps reorganizing the table.
  14. Replication is the process by which changes to a tables in a database as automatically moved to another database by the SQL Server processes. There are single-single, single-many, many-single, and multi-merge replication If you have data on one server that you need reflected on another server, replication can ensure that the data gets moved.
Steve Jones
December 2000
Return to Steve Jones Home

 

Total article views: 11536 | Views in the last 30 days: 5
 
Related Articles
BLOG

Querying Microsoft SQL Server : Basics of Indexes in SQL Server

Querying Microsoft SQL Server : Basics of Indexes in SQL Server: Indexes in SQL Server: If you see...

FORUM

Index

index

FORUM

Why would the same query use 2 different indexes based on date?

Query that is ran uses different indexes, Why?

ARTICLE

Stairway to SQL Server Indexes: Level 9, Reading Query Plans

Determining how, and if, SQL Server is using your indexes.

FORUM

always use incorrect index to run the query by sql server

always use incorrect index to run the query by sql server

Tags
career    
other    
rants    
state of the business    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones