sql

Query for second maximum salary from employee table

how to find out second maximum salary from all employee in employee table ? please give me sql query. ...

encrypt SQL connectionstring c#

Hello, I created an c# application (not asp webpage) which connects to a sql 2005 server. In my sourcecode the password and userid for this sql-server is coded plain text in ConnectionString. SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=server1;"+ "Initial Catalog=mydatabase;"+ ...

How to optimize a nested query?

Can I somehow join tables and avoid the usage of distinct in the following MySQL query. invited_by_id shows the user id of who invited this user. SELECT user1.id, count(distinct user2.id) AS theCount, count(distinct user3.id) AS theCount2 FROM users AS user1 LEFT OUTER JOIN users AS user2 ON user2.invited_by_id=user1.id LEFT...

Full text search problem

I have 2 tables nsk_UserInfo and Photos. I use full text search query to show result SELECT nsk_UserInfo.User_ID , nsk_UserInfo.Name , nsk_UserInfo.Contact_Person , nsk_UserInfo.Address, Photos.Photo1 as 'Photo' FROM nsk_UserInfo INNER JOIN Photos ON nsk_UserInfo.User_ID = Photos.User_ID WHERE FREETEXT ((nsk_UserIn...

Optimize sql query

I have a sql that takes 7 min to execute. It processes a year of data but even so i feel it takes too long. Do you have any suggerences to optimize it? select count(s.numserviciomedico) AS total from Actos a, pacientes p, Historias h, serviciosmedicos s where p.codigo=h.codpaciente AND p.codigo=a.codPaciente AND ...

Integer variable is acquiring a string value of "ad" somewhere along the line, can anyone see where?

Here is my code: I should get output of the department id (did) as an integer and the templatefilename (result) that is required. The errors I get are: Conversion from string "ad" to type 'Integer' is not valid. I'm fairly new to asp.net and cannot see where the did variable picks up the "ad" string. Any help would be greatly appreci...

does 'KEY' in SHOW CREATE TABLE mean a column is indexed?

I am dealing with a db I did not create. I will be issuing a number of long-running queries involving three integer columns of a large table: id (a primary key), x and y. (I won't be writing/updating records; just issuing queries.) SHOW CREATE TABLE shows that: `primary_key` int(11) NOT NULL auto_increment, `x` int(11) default NULL, `y...

PLS-00306: wrong number or types of arguments in call to existing stored procedure

hi , I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error. The Store procedure definition looks like : CREATE OR REPLACE FUNCTION SP( srq_id integer , ...

Oracle: "grep" across multiple columns?

I would like to perform a like or regexp across several columns. These columns contain tags, keywords, etc. Something that's the equivalent of: sqlplus scott/tiger @myquery.sql | grep somestring Right now I have something like select * from foo where c1 || c2 || c3 like '%somestring%' but I'm hoping I can get something a bit mor...

complex combine sql

query 1 : this will count all the record with email '[email protected]' select count(*) as total from userbase join extrauserinfofromhrms on userbase.username = extrauserinfofromhrms.useremail right join logevent on userbase.username = logevent.useremail join eachworkflow on logevent.workflowid= eachworkflow.workflowid where logevent.actio...

Algorithm for rdbms, select statement

hello dunno if this is the correct place to ask for this question,, im havin a thesis research and im in the algoritm now.. my thesis is an application that send messages using wherein the contacts will be query from the db.. so my question is what is the algorithm for searching the contacts from DB? linear search?? ...

what mysql query should i use to select a category that matches ALL my criteria?

Hi, i have the following data in my table called cat_product cat_id product_id 1 2 1 3 2 2 2 4 If given a set of values for product_id (2,3) i want to know the unique cat_id. In this case, that will be cat_id 1. how should i construct mysql query? i tried to use select distinct cat_id from cat...

Delete duplicate entries via SQL?

Is there any possibility in SQL to remove (only one) duplicate entries of composed columns (here: city, zip)? So if i have this SQL: INSERT INTO foo (id, city, zip) VALUES (1, 'New York', '00000') INSERT INTO foo (id, city, zip) VALUES (2, 'New York', '00000') Can i remove the first later with a sql statement? My approach doesn't wor...

How to add a large array to an SQL query?

This is my storm code: items = store.find((Delivery, Product, Sum(Delivery.quantity)), Delivery.id.is_in(order.id for order in self.orders), Product.id == Delivery.product_id) \ .group_by(Delivery.date, Product.id, Delivery.discount) \ .order_by(Delivery.dat...

SQL query with multiple indexes - SQL server 2000

I use a similar query like this select.....from.. with... (INDEX=IX_TABLE_1, INDEX=IX_TABLE_2)... I get the following error Only one list of index hints per table is allowed This seems to be working well with SQL Server 2005. Is it an issue with SQL server? ...

SQL batch separators in various DBMSes

I'm continuing my quest on creating a database migration framework, and I'm currently trying to implement proper support for native (platform-dependent) SQL scripts. From a SQL Server standpoint I know that a single .sql may contain several batches of SQL separated by GO terminators. GO itself is not a valid T-SQL keyword/whatever and i...

Optimize sql query

I have a sql that takes 1:20 min to execute. It processes a year of data but even so i feel it takes too long. I changed the IN uses with EXISTS as recommended for another query (in this case that optimization wasnt enought :S ) Do you have another suggerence to optimize it? select gd.descripcion,count(gd.descripcion) as total from dia...

Unselect the previous select

I don't know it may sound weird... but here is my situation... I have to select rows from two tables (Table1) and (Table2) Select * from Table1 Where <SomeCondition> Select * from Table2 Where <SomeCondition> But my constraint is If Table1 / Table2 return 0(zero) Rows... I should not return any results at all. That ie... If 1st sele...

What is the most easy way to get ddl sql-script from HSQLDB?

In other words, how to export sql-script for building database and putting data into it from HSQLDB? ...

what is the best way to create this database

hello what is the best way to create a database for Questions and Answers Script the user can submit a question with a set of choices among which the right answer exists the database must save the question, all the choices and the right answer. the best way that comes to my mind is to make 2 tables: Questions Table: ID Question Rig...