TSQL Knowledge Test.
Jan 12, 2004
Yo!
My company asked me to do a tech interview on a potential contractor for a three month assignment writing SQL Server stored procedures.
I have some things I can ask him to test his qualifications, such as cross-tab query design, UDF usage, running total queries, select first 'N' queries, Primary Keys vs Clustered Indexes, etc...
I was curious what questions other people might ask in order to assess a person's skill. Remember that this is SQL programming only, not admin stuff, and I want to ask questions with a range of difficulty from easy to head-cracker.
Any suggestions?
View 14 Replies
ADVERTISEMENT
Jan 22, 2008
Hi
I am looking for an interface connect to 'SQL Server 2000 enterprise' so that my TSQL can be executed. I am using 2003 Excel but there is a limitation of 65k records only can be loaded. Can I use 'SQL server express' 2000 or 2005, Acess?
If I want to use client of 'SQL server 2000' do I need to buy it? Which interfact has the query analyzer ?
Thanks in advanced
Daniel
View 3 Replies
View Related
Oct 12, 2015
We are setting up a test lab environment with 100 machines. We want one master testing db that gets replicated to each to run scripted application tests nightly.
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
View 6 Replies
View Related
Mar 13, 2008
I am loosing my mind on this one. I select info from a database. I then take that selected info, and select info from another table based on that. Straight forward. The CompanyKey attached to my username is 7 which displays in the ReaderResults.Text. I the following line:
selectSQL2 = "SELECT * FROM Company WHERE companyKey = ('" + CompanyKey + "')"
I can change it to:
selectSQL2 = "SELECT * FROM Company_Membership WHERE companyKey = 7 "
and it works. I have no idea what is going on. Dissapearing string? Here is the code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'Job1 InfoDim currentUserID
currentUserID = Context.User.Identity.Name.ToString()
Label1.Text = currentUserID
Dim selectSQL1 As String
selectSQL1 = "SELECT companyKey FROM Company WHERE UserID = ('" + currentUserID + "')"Dim cmd1 As New SqlCommand(selectSQL1, con)
Dim reader As SqlDataReaderDim CompanyKey
'Job2 Info
Dim selectSQL2 As String
selectSQL2 = "SELECT * FROM Company WHERE companyKey = ('" + CompanyKey + "')"Dim cmd2 As New SqlCommand(selectSQL2, con)
'Job1 Select
Try
con.Open()
reader = cmd1.ExecuteReader()Do While reader.Read()
CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
ReaderError.Text = "Error selecting record."
ReaderError.Text &= err.Message
Finally
ReaderResults.Text = CompanyKey
con.Close()
End Try
'Job2 Select
Try
con.Open()
reader = cmd2.ExecuteReader()
GridView1.DataSource = reader
GridView1.DataBind()
reader.Close()Catch err As Exception
ReaderError.Text = "Error selecting record."
ReaderError.Text &= err.Message
Finally
ReaderResults.Text = CompanyKey
con.Close()
End Try
View 7 Replies
View Related
Mar 31, 2004
Hi,
I need help to test person's skills for
knowledge of SQL stored procedures
and DTS
Could someone point on good resources.
Thank you
Alex
View 4 Replies
View Related
Jun 3, 2004
Hello, everyone:
Are ther any basic information about patch on SQL Server? How to develop patch, and run it? Thanks a lot
ZYT
View 7 Replies
View Related
Aug 29, 2006
Dear Friends, please tell me how can i gain domain knowledge in erp related modules?
View 3 Replies
View Related
Jul 22, 2007
Hello everyone...
i am new to Structured Query Language. I am learning it for the past 6 months form a book by myself. I don't have a teacher available to me. Now when i have finished that with all of the exercises completed. i want to make sure what i've learned is i've really learned it. the problem is that there is no such organization here that i join and have some hand on practical. So now i am asking u guys to help me. Tell me some way so that i make sure of whatever knowledge i have about SQL.
don't worry be happy
View 1 Replies
View Related
Jul 23, 2005
I do a lot of hiring for my company and a lot of the people I interviewsay that they are experts at SQL queries, but when I give them somethingsimple just beyond the typical SELECT type of queries, they choke.For example I have a table that looks like this:PK_ID - primary keyPARENT_ID - a FK to another row in the same tableThis essentially is a tree structure. I will ask interviewees to writea query that will return all the rows that have 2 direct children. Noone knows how to do this.I don't get it. I have done queries which boggle the mind and they arefar more complex than this.Am I asking too much?--* Don VaillancourtDirector of Software Development**WEB IMPACT INC.*phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.com/ This email message is intended only for the addressee(s)and contains information that may be confidential and/orcopyright. If you are not the intended recipient pleasenotify the sender by reply email and immediately deletethis email. Use, disclosure or reproduction of this emailby anyone other than the intended recipient(s) is strictlyprohibited. No representation is made that this email orany attachments are free of viruses. Virus scanning isrecommended and is the responsibility of the recipient./
View 15 Replies
View Related
Oct 17, 2006
Now that we have a good programming model in SSIS - the question is whether to write automated unit tests for your packages, and would it generally be a good idea for packages?
Also - if yes to write tests - then where to find more informations regarding How to accomplish that?
View 1 Replies
View Related
Nov 27, 2007
hi every one,
i need to test SSIS pacakge which will import data from different database where record count is around 5 millions.
iam planning to test it through c# code as well as manually also.
SSIS source : consist of 7 tables
SSIS destination :consist of 7 tables
Using c# code iam trying to run ssis package through batch file.
i am putting expected rowcount, column count in an excel file and comparing same with destination tables by writing query implementing ADO.Net concept.
am i going right way ,can any one suggest best and productive way to test the ssis package .
what are the other things i need to test it.
do any one can add test cases to it.
S.No
Test Case
1
Verify all the tables have been imported.
2
Verify all the rows in each table have been imported.
3
Verify all the columns specified in source query for each table have been imported
4
Verify all the data has been received without any truncation for each column.
5
Verify the schema at source and destination
6
Verify the time taken /speed for data transfer
7
Fields truncated due to difference in length of the field at destination.
Regards
Arif shareef
View 9 Replies
View Related
Sep 24, 2007
I am having a puzzling problem with a DTS package in SQL 2000 that uses a combination of "Execute Process Tasks" and "ActiveX Script Tasks." The issue occurs with one of (the second) the ActiveX Script Task. The script invokes a COM object that was written in C#. This COM object connects to the database and writes a record set and then calls a stored procedure.
When I run this step individually, everything works fine. When I run it as part of the package, the record set is being written but the stored procedure is not invoked (SQL Profile confirms this).
I haven't really begun a serious attempt to troubleshoot the problem, though I have played around with the Transactions and OLE DB properties of the DTS Package.
Any suggestions as to why the behavior might be different, or some straightforward tips on troubleshooting would be appreciated. I have access to the COM component source.
View 1 Replies
View Related
Nov 21, 2006
I dont know where to put this thread, I just wanna ask a question..actually what is happen when subscription synchronized the publications among IIS process.. (SQLMobile)
could you guys give me a diagram/schema of it?
thanks before
View 6 Replies
View Related
Jun 12, 2006
I don't have any knowledge about SQL Server at all... I wanna know:
1. What is SQL Server?
2. Have SQL Server something to do with databases and Microsoft Access to do?
3. What is the link between Visual C++ Express Edition and SQL Server Express Edition?
4. Where on the web can I learn (tutorials) how to use SQL Server Express Edition?
View 3 Replies
View Related
Feb 26, 2007
Dear Friends,
please suggest me the best book to gain sound knowledge in DBA concepts as well as
writing in sub queries, complex queries as well as inner queries.
thank you very much
Vinod
View 4 Replies
View Related
Nov 26, 2007
Hi,
I've been working as a .NET developer for many years. However, my educational background isn't in IT at all, so I occasionally find that there are big gaps in my knowledge when I'm called on to do certain things.
My current role is the first one I've had where the efficiency of my database calls has needed to be top-notch. Most of my previous posts have been in building intranet functionality for medium-sized companies so it's just not been an issue and my fairly basic level of SQL knowledge has been enough to get me by.
However, I'm increasingly finding I'm needing to look stuff up, ask for help and so forth and I'm not at all sure that what I'm putting out is up to scratch. I have three main areas of concern:
1) The developers here seem to like doing a lot of the "work" of data sorting and manipulation in SQL whereas I'd previously have just grabbed the whole table and done the manipulation in .NET. I'm finding building my complex queries is just doing my head in - I'm just not used to "thinking" in the way that SQL requires which seems very different from procedural programming.
2) A lot gets said about properly indexing tables for maximum efficiency. I know what an index is and vaguely what it does, but the means of applying them to ensure the best performance is pretty much beyond me.
3) In the same manner I'm now having to worry a lot more about performance issues in my database design. I now the five normal forms and so on, but is there anything else here I need to do to maximise performance?
What I'm asking, really, is whether anyone can recommend a good source for SQL knowledge (book, site, whatever) that specifically focuses on my first problem - it'll teach me the proper way to structure my queries and think about them properly so that they don't look so damn impenetrable. It then needs to go on and address my other issues.
Any suggestions?
View 4 Replies
View Related
Jul 20, 2005
Greetings,I have to admit that I'm still a beginner in the database field, but I'mactively studying, and this is why I will be utterly grateful for theproper, accurate, and wise guidance to the right direction or specificpaths of database studies.I simply want to make my first major database project a "personalknowledge database" or a "personal encyclopedia", so to speak. By this Imean that I want to gather diverse, multi-format bits of knowledge intoa single database divided into major categories, sub-categories, and soon. For example, I want to scan collected articles from newspapers &magazine and save them as images, type hand-written notes into thecomputer and save them as MS Word or Text files, save audio files withsearchable keywords, even save searchable MS OneNote audio & text files,all into directories & sub-directories. Then I want to be able to searchall these files for specific words or subjects; sort them according todate, subject, etc; update, add to or append, edit, or even cut & paste(within) any of those records in the database, which rely on easilycustomizable formats like text & images (in contrast with audio files orOneNote files, for example).I also want to prevent wasting time in the future, by avoidingrepetitive, same-keywords typing for every non-text new entry, like animage or audio file, by having a customizable keyword list open (byright clicking for example), where one could mark all the relevantkeywords for the picture or audio file. Also have a short descriptionfor each one of those entries when needed. And I want to be able to addslick, good-looking graphics to the interface of the database and theforms of the records.Basically, my priorities include: saving time filling the database withknowledge, quickly & accurately finding the future targets of anysearch, presenting the search results in a highly comprehensible &organized form, and a good looking interface & forms for every entry,which are pleasing to the eye and encouraging to study.I already use graphics applications for the "looks" part. So my questionis: what are the technologies or applications that I should learn byheart and use in order to design such a database in the best way? WouldMS Access alone do it all for me? Or should I learn other technologies?I'll be grateful for anyone taking the time to help. Thanks.Sincerely,Yasso"Claiming that God does not exist because there are people suffering anddying is like saying that barbers do not exist because there are peoplewith long hair! Truth is: they suffer because they did not find God ordo not go to Him, just like the others did not find a barber or do notgo to one."*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jan 17, 2006
Hello,
I've been trying for a couple of days now with the following problem in SQL
Ive a table called tblRresults that has the following
Date - just a regular date
OfficeLocation - just a unique id such as a zip code
SampleType - contains things like wall, floor, beam
SampleResult - "Detected" or "NotDetected"
When populated the table can look like
12/12/05 99505 Wall Detected
12/12/05 99505 Wall NotDetected
10/04/05 99211 Beam Detected
10/04/05 99211 Beam Detected
10/04/05 99111 Floor NotDetected
10/04/05 99111 Floor NotDetected
What I want is (I guess) a crosstab query that produces the following
Date Location SampleType CountDetected CountNotDetected
12/12/05 99505 Wall 1 1
10/04/05 99211 Beam 2 0 (or null would be ok)
10/04/05 99111 Floor 0 2
Any help gratefully accepted, this has been driving me mental!
thanks
View 2 Replies
View Related
Jun 25, 2015
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
View 4 Replies
View Related
Nov 19, 2007
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
View 1 Replies
View Related
Feb 22, 2008
I am trying to test to see if my code is returning rows. If it's not I want to display an error saying "Nothing Found" Please review and give me your thoughts on the best way to accomplish this.
1 Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click
2 Dim SubEmail As String
3 Dim SubPassword As String
4 SubEmail = txtNewsEmails.Text
5 SubPassword = txtNewsPassword.Text
6 Session("NewsEmail") = SubEmail
7 Session("NewsPassword") = SubPassword
8 Dim sID As Integer
9
10 Dim cs As String = ConfigurationManager.ConnectionStrings("csTiPs3").ConnectionString
11 Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection(cs)
12 cn.Open()
13 Dim selectString As String = "Select SubscriberID from NewsletterSubscribers WHERE SubscriberEmail = '" + SubEmail + "' AND SubscriberPassword = '" + SubPassword + "'"
14
15 Dim cmd As SqlClient.SqlCommand = New SqlCommand(selectString, cn)
16
17 Dim reader As SqlDataReader
18 reader = cmd.ExecuteReader
19 While reader.Read()
20 sID = reader("SubscriberID")
21 End While
22 Session("SubscriberID") = sID
23
24 reader.Close()
25
26 rtsNewsletters.SelectedIndex = 1
27 rtsNewsletters.FindTabByText("Subscribe").Enabled = True
28 rmpNewsletters.SelectedIndex = 1
29
30 End Sub
Thanks all,
Brett
View 1 Replies
View Related
Mar 8, 2007
Duplicate post
View 2 Replies
View Related
Jul 24, 2006
I recently lost my job and wanted to do some test development to keep my skills up to date. The problem is I don't have access to any data sources. Is there such a thing around? I tried installing microsoft's trial of SQL 2005 but can't get it to run on my laptop and SQL 2000 trial no longer exists. I simply need to create a SQL db/tables etc. Thanks in advance.
View 9 Replies
View Related
Sep 25, 2007
Hi!
I am currently utilizing the checksum function to generate a hash that I later compare to detect changes in a row.
CHECKSUM(field1, field2, field3, field4)
Now I'd like to use the HashBytes function instead over the same fields. But the HashBytes function accepts only one data value. What is the most effective and reliable way of getting an MD5 over several fields?
Thanks
View 3 Replies
View Related
Apr 10, 2008
Hi.
I have an application where I allow users to type in their SQL queries. Before I store those queries I have to make sure that they are correct, both syntax-wise and data type-wise. For that I execute the query against the database and trap any errors that may be returned and that's how I judge if the query was OK.
In order to keep this test as quick as possible, I tried to add a WHERE clause to it like: WHERE 1=2, so no results are returned. But then I discovered that the addition keeps errors from happening if they are of ata type nature.For instance "select orderid + 'test' from orders where 1=2", run against the Northwind database, returns no errors, while OrderID is numeric and U'm adding a string to it!
Next, I tried to return only one row: "select top 1 orderid + 'test' from orders". This time the error is thrown, however the query still takes a looong time when run on a huge table. I don't kknow why that is, but it seems that the engine runs the query for the entire table and then gets the first row!
Does anyone have an idea ehat's happening or have a better suggestion on how I can perform my test without killing the database?
Thanks.
View 4 Replies
View Related
Nov 18, 2005
I have a stored procedure which runs a query, then turns it on its side (so that the rows are column headers).What I need to do now is test if any of the values in a row from this stored procedure are null.Any suggestions on how to get started? Is there some function that will tell me if any value in the row is null?
View 2 Replies
View Related
Oct 14, 2002
I need to refresh a test db that is running on the same instance as the prod db. Should I just use DTS or is there a better way?
View 3 Replies
View Related
Mar 24, 2005
Hello Everyone,
What is the best way to test connectivity with the database from the application on a frequest basis?
Thanks.
View 6 Replies
View Related
May 1, 2006
Hi -
I'm having a problem with a VB6 program I'm writing that posts data to a SQL server. The problem occurs when the SQL server is unavailable. When this happens it causes the program to hang for at least 30 seconds before releasing. The hang happens at the code that connects to the SQL server, because the server isn't there. I've added code found on this site to test the connectivity prior to making my actual data connection, but this causes the same hang. Is there any way to verify connectivity to an SQL server without actually trying to make the connection? I'm really trying to eliminate the hang that occurs when the server is unavailable.
Thanks
View 13 Replies
View Related
May 17, 2007
I have gotten some criticism from coworkers regarding this test and just wanted to see what you guys think. I realize the wording could use improvement and any criticism towards making it easier to understand is much appreciated.FWIW - I had to solve this problem on the job so I feel it is a real-world test that helps me understand how people think and if they try to find alternate solutions.Thanks!~~~~~~~~~~~~~~~~~~~~Given a table that has over 100,000 records…SUBSIDIARY=========PARENT_IDINTCHILD_IDINTULTIMATE_PARENT_IDINTCLEANUP_INDBIT…where each PARENT_ID can have multiple CHILD_ID values, but the PARENT_ID should not equal the CHILD_ID. After an initial data load, the ULTIMATE_PARENT_ID and CLEANUP_IND columns contain NULL values (see page 2 for sample data).ULTIMATE_PARENT_ID is defined as the topmost parent in the chain for the particular CHILD_ID record, so if the chain was only 2-level’s deep the ULTIMATE_PARENT_ID is the CHILD_ID’s PARENT_ID’s PARENT_ID.Please write an answer for all three questions below:A)Which of the following queries should you run first?B)Write an optimized query to identify the ULTIMATE_PARENT_ID for each CHILD_ID and set its value into the ULTIMATE_PARENT_ID column.C)Write a query to identify ALL of the circular references and mark each record that is a circular reference by updating the CLEANUP_IND column to 1.~~~~~~~~~ Page 2 ~~~~~~~~~ Sample Data, remember though this table has over 100,000 records and the parent-child chain can go n-levels deep – where n is not known.PARENT_IDCHILD_IDULTIMATE_PARENT_IDCLEANUP_IND1024512NULLNULL362300NULLNULL887541NULLNULL10221024NULLNULL546887NULLNULL5122305NULLNULL112967NULLNULL697123NULLNULL901452NULLNULL2300666NULLNULL334445NULLNULL512903NULLNULL884554NULLNULL313313NULLNULL554884NULLNULL112119NULLNULL967555NULLNULL2305333NULLNULL33336NULLNULL541546NULLNULL10301020NULLNULL112999NULLNULL
View 14 Replies
View Related
Aug 1, 2007
This is a test post
View 7 Replies
View Related
