- Local variables denoted as
- Global variables
@@rowcount- Holds the number of rows returned by the last Transact-SQL statement. Be careful, almost any statement will set this. Even an “if” statement which checks it’s value. For example:
@@error- Holds status of last statement executed. Zero is success. Once again almost any statement sets this, so use it’s value immediately (or save it in a local variable).
@@servername- The name of the Sybase dataserver.
@@version- What version of the Sybase server you are using
- Local variables denoted as
1 2 3
- What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?
- DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.
- TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
- Co-related subquery? Co-related query is a query in which subquery depends on execution of main query
- How to select first/last/max per group in SQL?
- SQL query to get 4th or 5th maximum value from a table
1 2 3
- Difference between
ONclause in joins?
USINGclause allows you to specify the join key by name.
ONclause allows you to specify the column names for join keys in both tables.
ONclause preserves the columns from each joined table separately, which the
USINGclause merges the columns from the joined tables into a single column.
USINGmay not be a good idea when using outer joins where you would want to see unmatched rows from a table.
1 2 3 4 5
- When we need to use USING clause in the sql? For example in this below:
1 2 3
- How to delete duplicate records in a table?
- What will be the output of this query?
SELECT 1 FROM DUAL UNION SELECT 'A' FROM DUAL;The query would throw an error. The two data types in the union set should be same. Out here it is a 1 and ‘A’, datatype mismatch and hence the error.
- What does
UNIONdo? What is the difference between
UNIONmerges the contents of two structurally-compatible tables into a single combined table.
- The difference between
UNION ALLis that
UNIONwill omit duplicate records whereas
UNION ALLwill include duplicate records.
UNION ALLis typically be better than
UNIONrequires the server to do the additional work of removing any duplicates.
- What will be the result of the query below? Explain your answer and provide a version that behaves correctly?
This query will actually yield “Nope”, seeming to imply that
null is not equal to itself! The reason for this is that the proper way to compare a value to
null in SQL is with the
is operator, not with
- What will be the result of the query below?
SELECT * FROM runners;
SELECT * FROM races;
|1||100 meter dash||2|
|2||500 meter dash||3|
SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races)
Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL
NOT IN condition contains any values that are
null, then the outer query here will return an empty set, even if there are many runner ids that match winner_ids in the races table.
Knowing this, a query that avoids this issue would be as follows:
- Given a table SALARIES, such as the one below, that has m = male and f = female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.
- Write a SQL query using
UNION) that uses the
WHEREclause to eliminate duplicates. Why might you want to do this?
You can avoid duplicates using
UNION ALL and still run much faster than
UNION by running a query like this:
The key is the
AND a!=X part. This gives you the benefits of the
UNION command, while avoiding much of its performance hit.
- What are the
NVL2functions in SQL? How do they differ?
NVL(exp1, exp2) and
NVL2(exp1, exp2, exp3) functions check the value
exp1 to see if it is null.
NVL(exp1, exp2) function, if
exp1 is not null, then the value of
exp1 is returned; otherwise, the value of
exp2 is returned, but case to the same data type as that of
NVL2(exp1, exp2, exp3) function, if
exp1 is not null, then
exp2 is returned; otherwise, the value of
exp3 is returned.