MS SQL Server Concepts and Programming Interview Questions and Answers

1 :: PHP MSSQL - How To Create an Identity Column?

Many tables require an ID column to assign a unique ID number for each row in the table. For example, if you have a table to hold forum member profiles, you need an ID number to identify each member. To allow SQL Server to automatically assign a new ID number for each new record, you can define the ID column with IDENTITY attribute as shown in the following sample script:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "CREATE TABLE ggl_users ("
. " id INTEGER IDENTITY NOT NULL"
. ", name VARCHAR(80) NOT NULL"
. ", email VARCHAR(80) NULL"
. ", time DATETIME NULL"
. ")";
$res = mssql_query($sql, $con);
if (!$res) {
print("Table creation failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
print("Table ggl_users created. ");
}

mssql_close($con);
?>

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

2 :: PHP MSSQL - How To Query Multiple Tables Jointly?

If you want to query information stored in multiple tables, you can use the SELECT statement with a WHERE condition to make an inner join. Assuming that you have 3 tables in a forum system: "users" for user profile, "forums" for forums information, and "posts" for postings, you can query all postings from a single user with a script as shown below:

<?php
$con = mssql_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

$userID = 101;
$sql = "SELECT posts.subject, posts.time, users.name,"
. " forums.title"
. " FROM posts, users, forums"
. " WHERE posts.userID = ".$userID
. " AND posts.userID = users.id"
. " AND posts.forumID = forums.id";
$res = mssql_query($sql, $con);
while ($row = mssql_fetch_array($res)) {
print($row['subject'].", ".$row['time'].", "
.$row['name'].", ".$row['title']." ");
}
mssql_free_result($res);

mssql_close($con);
?>

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

3 :: PHP MSSQL - How To Perform Key Word Search in Tables?

The simplest way to perform key word search is to use the SELECT statement with a LIKE operator in the WHERE clause. The LIKE operator allows you to match a text field with a keyword pattern specified as '%keyword%', where (%) represents any number of any characters. Any single quote (') in the keyword needs to be protected by replacing them with two single quotes (''). The tutorial exercise below shows you how to search for records whose "notes" contains "e":

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$key = "e";
$key = str_replace("'", "''", $key);
$sql = "SELECT id, url, notes FROM ggl_links"
. " WHERE notes LIKE '%".$key."%'";
$res = mssql_query($sql, $con);
while ($row = mssql_fetch_array($res)) {
print($row['id'].", ".$row['url'].", "
. $row['notes']." ");
}
mssql_free_result($res);

mssql_close($con);
?>

If you run this script, you will get something like this:

102, www.GlobalGuideLine.com, Nice site.
202, www.yahoo.com, It's another search engine!
301, netsc

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

4 :: PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes?

You have seen a lots of Websites are displaying past times in days, hours and minutes. If you want to do this yourself, you can use the DATEDIFF() SQL function The following tutorial exercise shows you how to use DATEDIFF() to present a past time in days, hours, and minutes:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$submit_time = "2007-05-29 04:09:49";
$sql = "SELECT 'Posted '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())/(24*60))"
. " + ' days, '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())%(24*60)/60)"
. " + ' hours, and '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())%60)"
. " + ' minutes ago.'";
print(" $sql ");

$res = mssql_query($sql, $con);
if (!$res) {
print("SQL statement failed with error: ");
print(" ".mssql_get_last_message()." ");
} else

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No

5 :: PHP MSSQL - How To Include Date and Time Values in SQL Statements?

If you want to provide date and time values in a SQL statement, you should write them in the format of "yyyy-mm-dd hh:mm:ss", and quoted with single quotes ('). The tutorial exercise below shows you two INSERT statements. The first one uses a hard-code date value. The second one uses the date() function to return a date value representing current date and time.

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$notes = "Added long time ago!";
$time = "1999-01-01 01:02:03";
$sql = "INSERT INTO ggl_links (id, url, notes, time)"
. " VALUES ("
. " 301, 'netscape.com', '".$notes."', '".$time."')";
if (!mssql_query($sql, $con)) {
print("SQL statement failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
print("1 rows inserted. ");
}

$notes = "Added today!";
$time = date("Y-m-d H:i:s");
$sql = "INSERT INTO ggl_links (id, url, notes, time)"
. " VALUES ("
. " 302, 'myspace.com', '".$notes."', '".$time."')";
if (!mssql_query

0/5 Rating (0 vote)

Is This Answer Correct?    0 Yes 0 No