SQL Programming: Questions and Answers

549 Pages·2016·5.52 MB·English

by  Duckett George A.

Table of Contents
About this book
SQL (3 questions)
SQL Server (129 questions)
MySQL (85 questions)
Tsql (59 questions)
Database (47 questions)
SQL Server 2005 (26 questions)
Oracle (23 questions)
SQL Server 2008 (18 questions)
PostgreSQL (17 questions)
Performance (15 questions)
JOIN (14 questions)
DateTime (13 questions)
Database Design (13 questions)
Select (10 questions)
PHP (9 questions)
C# (8 questions)
Java (8 questions)
GROUP BY (8 questions)
SQLite (6 questions)
DISTINCT (6 questions)
Django (5 questions)
SSMS (5 questions)
LINQ To SQL (5 questions)
Random (4 questions)
Duplicates (4 questions)
NoSQL (4 questions)
LINQ (3 questions)
ORM (3 questions)
Foreign Keys (3 questions)
CASE (3 questions)
String Concatenation (3 questions)
Case Sensitive (3 questions)
Indexing (2 questions)
Triggers (2 questions)
Sql Order By (2 questions)
Hierarchical Data (2 questions)
HTML (1 question)
Eclipse (1 question)
Sorting (1 question)
Console (1 question)
UNION (1 question)
Ms Access (1 question)
Anti Patterns (1 question)
Core Data (1 question)
Dynamic SQL (1 question)
Cursor (1 question)
Copyright
About this book
This book has been divided into categories where each question belongs to one or more
categories. The categories are listed based on how many questions they have; the question
appears in the most popular category. Everything is linked internally, so when browsing a
category you can easily flip through the questions contained within it. Where possible
links within questions and answers link to appropriate places within in the book. If a link
doesn’t link to within the book, then it gets a special icon, like this .
SQL
Skip to questions,
Wiki by user john-saunders
SQL stands for Structured Query Language.
One subset of the SQL standard is DDL (Data Definition Language), which is used to
create tables and constraints. These include:
CREATE
DROP
ALTER
Another subset is DML (Data Manipulation Language), which is used to modify and view
data within the database:
SELECT
INSERT
UPDATE
DELETE
The final “standard” subset of commands is DCL (Data Control Language):
GRANT
REVOKE
Many database implementations require the use of SQL, and over the years, vendors have
implemented dialects of SQL to provide more functionality as well as simplify it. Because
of these deviations from the standard, SQL is fractured - syntax that works on one
implementations does not necessarily work on another.
ISO/IEC (formerly ANSI) standards have been beneficial in resolving such situations, but
adoption is selective. Queries conforming to these standards should be portable to other
databases, though performance may vary.
Most DBMSs have additional languages for writing stored procedures. In Oracle this is
PL/SQL (Procedural Language/Structured Query Language), in PostgreSQL it’s
PL/pgSQL (Procedural Language/PostgreSQL). Outside of stored procedures or functions,
Oracle and PostgreSQL use SQL. Thus the tags plsql and plpgsql should only be
used for problems directly related to writing stored procedures. Microsoft SQL Server
uses the term T-SQL (Transact-SQL)(tsql) for both “plain” SQL (queries, DML, ..) and the
language used for stored procedures.
Tagging Recommendation
This tag should be used for general SQL programming language questions, in addition to
tags for specific products. For example, questions about Microsoft SQL Server should use
the sql-server tag, while questions regarding MySQL should use the mysql tag. SQL is the
umbrella under which these products exist; tagging them by product (including version,
e.g oracle11g , sql-server-2008) is the easiest way to know what functionality is
available for the task at hand. It is very common for mysql questions to omit this tag
because query discussions on MySQL are more often stated as MySQL rather than SQL in
general.
Please read this summary about the SQL standard (the 1992 one in this case, broadly
implemented) and if you can, refer to the book itself .
Free SQL Programming Books
Developing Time-Oriented Database Applications in SQL
Use The Index, Luke!: A Guide To SQL Database Performance
Learn SQL The Hard Way
SQL Tutorial For Starters
SQL - Free books
SQL - Free books 2
Free SQL/Database Online Courses
Coursera-Introduction to Databases
Stanford Online-DB: Introduction to Databases
SQL/Database Online Tutorial.
Codeschool Try SQL
Useful Resources
While you should always provide complete code examples (e.g. schema, data sample and
expected result) in your question or answer, you can also isolate problematic code and
reproduce it in an online environment such as SQL Fiddle , and link to that.
MySQL is managed and maintained by Oracle and in-depth documentation can be found
at the MySQL website .
More specific tags
When you are asking a question about SQL you can also add more specific tags. Here is
the list of available tags:
sqlbulkcopy
sqlconnection
sqlcommand
sql-copy
sql-convert
sql-delete
sql-drop
sqldatetime
sql-date-functions
sql-function
sql-job
sql-like
sql-limit
sql-merge
sql-order-by
sql-returning
sql-server-job
to-date
triggers
sql-timestamp
sql-update
sql-view
stored-procedures
having
where-clause
count
group-by
join
left-join
inner-join
outer-join
self-join
cross-join
right-join
full-outer-join
natural-join
Implementation specific tags
You can specify your question by adding the implementation you used as a tag.
mysql
mysqli
sql-server
oracle
postgresql
sqlite
db2
Questions
Q: In SQL, what’s the difference between count(column) and count(*)?
Tags: sql (Next Q)
I have the following query:
select column_name, count(column_name)
from table
group by column_name
having count(column_name) > 1;
What would be the difference if I replaced all calls to count(column_name) to count(*)?
This question was inspired by How do I find duplicate values in a table in Oracle?.
To clarify the accepted answer (and maybe my question), replacing count(column_name)
with count(*) would return an extra row in the result that contains a null and the count
of null values in the column.
Tags: sql (Next Q)
User: bill-the-lizard
Answer by sqlmenace
count(*) counts NULLs and count(column) does not
[edit] added this code so that people can run it
Skip code block
create table #bla(id int,id2 int)
insert #bla values(null,null)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,null)
select count(*),count(id),count(id2)
from #bla
results 7 3 2
Tags: sql (Next Q)
Q: What is the reason not to use select *?
Tags: sql (Prev Q) (Next Q)
I’ve seen a number of people claim that you should specifically name each column you
want in your select query.
Assuming I’m going to use all of the columns anyway, why would I not use SELECT *?
Even considering the question *SQL query - Select * from view or Select col1, col2, …
colN from view *, I don’t think this is an exact duplicate as I’m approaching the issue
from a slightly different perspective.
One of our principles is to not optimize before it’s time. With that in mind, it seems like
using SELECT * should be the preferred method until it is proven to be a resource issue or
the schema is pretty much set in stone. Which, as we know, won’t occur until development
is completely done.
That said, is there an overriding issue to not use SELECT *?
Tags: sql (Prev Q) (Next Q)
User: notme
Answer by robert-paulson
The essence of the quote of not prematurely optimizing is to go for simple and
straightforward code and then use a profiler to point out the hot spots, which you can then
optimize to be efficient.
When you use select * you’re make it impossible to profile, therefore you’re not writing
clear & straightforward code and you are going against the spirit of the quote. select * is
an anti-pattern.
So selecting columns is not a premature optimization. A few things off the top of my head
….
1. If you specify columns in a SQL statement, the SQL execution engine will error if
that column is removed from the table and the query is executed.
2. You can more easily scan code where that column is being used.
3. You should always write queries to bring back the least amount of information.
4. As others mention if you use ordinal column access you should never use select *
5. If your SQL statement joins tables, select * gives you all columns from all tables in
the join
The corollary is that using select * …
1. The columns used by the application is opaque
2. DBA’s and their query profilers are unable to help your application’s poor
performance
3. The code is more brittle when changes occur
4. Your database and network are suffering because they are bringing back too much
data (I/O)
5. Database engine optimizations are minimal as you’re bringing back all data
regardless (logical).
Writing correct SQL is just as easy as writing Select *. So the real lazy person writes
proper SQL because they don’t want to revisit the code and try to remember what they
were doing when they did it. They don’t want to explain to the DBA’s about every bit of
code. They don’t want to explain to their clients why the application runs like a dog.
Tags: sql (Prev Q) (Next Q)
Q: What’s the difference between NOT EXISTS vs. NOT IN vs. LEFT
JOIN WHERE IS NULL?
Tags: sql (Prev Q)
It seems to me that you can do the same thing in a SQL query using either NOT EXISTS,
NOT IN, or LEFT JOIN WHERE IS NULL. For example:
SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)
SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a)
SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL
I’m not sure if I got all the syntax correct, but these are the general techniques I’ve seen.
Why would I choose to use one over the other? Does performance differ…? Which one of
these is the fastest / most efficient? (If it depends on implementation, when would I use
each one?)
Tags: sql (Prev Q)
User: froadie
Answer by quassnoi
NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL
NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle
NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL
In a nutshell:
NOT IN is a little bit different: it never matches if there is but a single NULL in the list.
In MySQL, NOT EXISTS is a little bit less efficient
In SQL Server, LEFT JOIN / IS NULL is less efficient
In PostgreSQL, NOT IN is less efficient
In Oracle, all three methods are the same.
Tags: sql (Prev Q)