sql

Learning PostgreSQL

What's the best place to start learning PostgreSQL administration and programming in a Linux environment? What websites are the most recommended, concise and have easy to learn tutorials? I am already familiar with ANSI SQL from playing with SQL Server and MySQL; however, I've never touched PostgreSQL before. Any direction you can give...

Is it possible to make SP/Function as private ones?

I am using Microsoft SQL 2005 server. I have created several SP and Functions in TSQL. After more than one year's development, the number of SP/Functions have grown dramatically. Only a few of SP/Functions are used as scheduled jobs or triggers, and most of them are used internally(I mean they are called by SP/Functions in jobs and trigg...

SQL Running Sum with flags at certain amounts

I am trying to create a query to sum total gifts given by a certain person, but to add a flag on the individual gift when the total giving hits $500 (I don't need to sum past that). I have the Persons ID, the gift size, and the gift date. for example: ID gift_date gift_amt --------- ----------- -------------- 1...

How to rename a column in SQL?

What is the best practice when it comes to renaming a table column using SQL (MS SQL Server 2005 variant)? This assumes that there is data in the column that must be preserved. ...

Combining INSERT and UPDATE statement (SQL2005 Stored Procedure)

Hi, I need to extract records from a table, copy the data to a second table and then update the records in the first table to indicate that they have been copied across successfully. My current SP code is this: SELECT TBL_ADDRESSBOOKADDRESSES.* FROM TBL_ADDRESSBOOKADDRESSES INNER JOIN TBL_CAMPAIGNS ON TBL_ADDRESSBOOKADDRESSES.adds_AB...

Determine what user created objects in SQL Server

I'm trying to go through our development DB right now and clean up some of the old test procs/tables. Is it possible to determine what user created objects in a SQL Server 2005 database? If so, how would I go about finding that information? Edit: Just wanted to clarify that the objects in question already exist. Setting up auditing and ...

PL/SQL block problem: No data found

SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := &sv_student_id; v_section_id NUMBER := 89; v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN SELECT final_grade INTO v_final_grade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; CASE -- outer CASE WHEN v_final_grade IS NULL THEN DBMS_OUTP...

Filtering LEFT JOIN results

I have two tables: authorizations and settlements. 'Settlements' contains a forign key reference to authorizations. A settlement can also have a status (ERROR, ACCEPTED, etc). Given this data: Authorizations Settlements id id | auth_id | status ----- --------------------------- 1 ...

SQL Joining with Imperfect Match

Is it possible to match the following combination of such keys in SQL? The key values like an array and delimiter = '/'. Key ----- A/B/C A B C A/B A/C B/C ...

SQL Server 2005 longest running query per database

Hi all, I am using this SELECT * FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC to get the longest running queries on the server level. How do I get the longest running queries per database? I would like to focus only on one database at a time. Thanks, Edi ...

Entity Framework Generated SQL

Is it possible to output the SQL generated by the Entity Framework on a global level and not on a query by query basis? I'm looking to just dump it all somewhere so I can review it. If that is not possible, how do I view the SQL being generated for updates and inserts? ...

sql max/min query and data transformation

update: changed one time to show that the times per shipment may not be in sequential order always. here is my input create table test ( shipment_id int, stop_seq tinyint, time datetime ) insert into test values (1,1,'2009-8-10 8:00:00') insert into test values (1,2,'2009-8-10 9:00:00') insert into test values (1,3,'2009-8-10 10:00...

mysql show rows in order from highest value to lowest value

I have a table that contains a column named views. Each time the user refreshes the page, it updates the view count +1. What im trying to do, is create a mysql query to output the list of rows based on their views count, from highest to lowest. this is what i thought would work SELECT * FROM picdb ORDER BY views DESC LIMIT 10 even wh...

Programming first, framework second?

Firstly hello as my first question. Looking for guidance rather than coding fix. The final flicker of Informix 4gl contracting extingiushed for me in 2004. To cut a long story short I am looking to code again by creating a website. I will be using PHP v5 and MySQL. Spent about a year (in spare time) doing all data analysis and DB desig...

SQL Sub-query -- Select JobID with a maximum JobValue

This seems like an easy thing, but I'm drawing a blank. Select * from .... inner join ( select JobsID, Value from Jobs where Value **is the highest** ) as MaxJob on MaxJob.CustID = A.CustID inner join ( select other information based upon MaxJob.JobID ) as OtherStuff Is there a nice way to have that first subquery gi...

Writing an app to interact with SQL 2008 without managed code

I want to write a winPE(vista) application that connects to a DB and just writes a row in a table saying it booted winPE. Here is my problem. I only ever do .NET. I'm pretty familiar with OO concepts so I'm finally taking the plunge to unmanaged code. I assume I have to use visual studio's unmanaged c++ project type, but I don't know wh...

complex sql tree rows

table structure id | message | reply_id 1 | help me! | 0 434 | love to | 1 852 | didn't work | 434 0110 | try this | 852 2200 | this wont | 0 5465 | done. :) | 0110 i have a id "852" which is middle of tree menu, but i want to get all the previous related and next re...

Synchronizing N Values Accross Tables in SQL.

Disclaimer: I just started with databases, so I'm probably missing something extremely trivial. I have two (somewhat related, but not enough to be a single table) tables: table_one and table_two. table_one has two columns of significance: name of type varchar(n) intersects of type bit table_two has one column of significance: n...

Preserve SQL Indexes While Altering Column Datatype

I have a smalldatetime column that I need to alter to be a datetime column. This is something that will be part of an install process, so it cannot be a manual procedure. Unfortunately, the column has a few indexes and a not null constraint on it. The indexes are performance related and would need to be retained only using the new data t...

Looking for an SQL scripting language to manipulate DBF files.

I have a large collection of DBF files (about 1100 of them) that I need to analyze for a client. Each file contains a single table. I need to perform a query on each table, copy the results into one large results table (which will hold the results from all files), and then move on to the next DBF file. At the end I need to save the resul...