sql

Alter table add column syntax in SQL

I'm trying to alter the table named company, but it will display the error syntax error at or near "(" LINE 2: ADD( company_access_level short NOT NULL, My syntax is ALTER TABLE company ADD company_access_level short NOT NULL, workgroup_level short NOT NULL, Company Logon URL character varying NOT NULL, Company Logoff URL character...

Found a weak escape function for MySql, how to exploit?

In an application I'm working on I've found a weak escape function to prevent injection. I'm trying to prove this, but I'm having trouble coming up with a simple example. The escape function works as follows (PHP example). function escape($value) { $value = str_replace("'","''",$value); $value = str_replace("\\","\\\\",$value); ...

find missing values in MySQL

Hi all, I am trying to write a query to find out what users are not enrolled on some courses. I have 2 tables; courses, and users. Both contain the fields 'id' and then 'coursename' and username' respectively. Using these two table the existing system adds users to a third table (called enrolled_users). Users take up one row for each. ...

By hour usage query in sql

Hi guys, hope you can help me. I've a DB table, lets call it activities [activities] [start_time - datetime] [end_time - datetime] [name - string] i need a query which will return me, for each hour of a 24day how many activites were active at that time. for example: [time] [usage] 11:00 (11 am) 12 etc... ...

how do I create a bounded return from mysql?

I have a list of users, there is a column 'points' and I need to create a list of the ten people with lower points and the ten people with higher points than the user. The only trouble is I have no way of knowing at this stage what their points will be so I can't use hard values. Help? Thanks in advance :) ...

sql: how to improve this statment.

Hi, I have the following sql statement that I need to make quicker. There are 500k rows, and I an index for 'HARDWARE_ID', but this still take up to a second to perform. Does anyone have any ideas? select * from DEVICE_MONITOR DM where DM.DM_ID = ( select max(DM_ID) ...

Changing the RoR SQL call to NAME instead of *

Being that it is better to call a SQL database with the specific item, for example: Bad: SELECT * FROM tblUsers Good: SELECT email FROM tblUsers How would I do this in RoR to make it explicitly perform the latter example? ...

Monitor SQL Server for new row

I'm creating an application that notifies users if a new row has been added to a table in the applications database. Would it be best to create a trigger for this or just check every few seconds directly from the application? Thoughts? ...

SQL Job using OPENQUERY Login failed

I have a job that I'd like to run in SQLServer 2008 that needs to get some data from our SQLServer 2000 db so I'm using OPENQUERY to get that data. When I execute the job I get the following error: Executed as user: SEDONAGROUP\sqlage. Login failed for user 'SEDONAGROUP\sqlage'. [SQLSTATE 28000] (Error 18456). The step failed. The St...

Print equivalent on Oracle PLSQL

When you execute the following block in Oracle SQL Developer set serveroutput on format wraped; begin DBMS_OUTPUT.put_line('the quick brown fox jumps over the lazy dog'); end; You get the following response anonymous block completed the quick brown fox jumps over the lazy dog I am basically trying to use PRINT so I can track prog...

Subsonic 2.2 SqlQuery with Inner Join and Where

I am trying to convert the following SQL into Subsonic syntax using the SqlQuery functionality: SELECT DISTINCT * FROM FamilyMemberTeamRole FMTR INNER JOIN TeamRole TR ON FMTR.TeamRoleId = TR.TeamRoleId INNER JOIN Team T ON T.TeamId = TR.TeamId LEFT JOIN FamilyMemberClassHistory FMCH ON FMCH.FamilyMemberClassHistoryId = FMTR.FamilyMemb...

RSQLite query with user specified variable in the WHERE field

I am using a the RSQLite library in R in to manage a data set that is too large for RAM. For each regression I query the database to retrieve a fiscal year at a time. Now I have the fiscal year hard-coded: data.annual <- dbGetQuery(db, "SELECT * FROM annual WHERE fyear==2008") I would like to make the fiscal year (2008 above) to make ...

SQL: GROUP BY records and then get last record from each group ?

I have a table like this: id| name | attendence 1 | Naveed| 1 2 | Naveed| 1 3 | Adil | 1 4 | Adil | 1 I use following query: SELECT * FROM `test` WHERE `attendence`=1 GROUP BY name The result with above query: id| name | attendence 3 | Adil | 1 1 | Naveed | 1 Question: Above result group rows by name but show first row f...

SQL Server 2008- Keywords

Hi I've looked everywhere for an answer and cannot find one. I was reading an sql tutorial which used the keyword EXPLAIN to see how a query is executed. I tried the in SQL Server 2008 with no success. So my question is. What word would I use to get the equivelant result? Thank you in adv. ...

Get words from sentence - SQL

Hi Suppose I have a description column that contains Column Description ------------------ I live in USA I work as engineer I have an other table containing the list of countries, since USA (country name) is mentioned in first row, I need that row. In second case there is no country name so I don't need that column. Can you plea...

shuffle values in an integer column so they are always unique and sequential

I have a table I'd like to sort with a "priority" column. This column needs to be reordered when the priority of a record is changed or records are removed. Think of it as an array. The values will be modified in a UI so I want them to remain whole numbers and represent the true position within the larger recordset. The priority column w...

Is there a SQL ANSI way of starting a search at the end of table?

In a certain app I must constantly query data that are likely to be amongst the last inserted rows. Since this table is going to grow a lot, I wonder if theres a standard way of optimizing the queries by making them start the lookup at the table's end. I think I would get the same optmization if the database stored data for the table in...

SQL merging two tables and updating referenced IDs

I have two tables that I want to join into one table and use a TypeID to differentiate them. Let's say the types are A and B. The Tables are A_Level and B_Level A's Table looks like Level Level_ID Description B's Table looks like Level Level_ID Level_Desc A's Level_ID is referenced from Table C as Level_ID B's Level_ID is...

The SELECT permission was denied on the object 'Notes', database 'Sticky', schema 'dbo'.

It appears here: SyncAdapter notesSyncAdapter = notesBuilder.ToSyncAdapter(); ((SqlParameter)notesSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_last_received_anchor"]).DbType = System.Data.DbType.Binary; ((SqlParameter)notesSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_new_rec...

Does SQL*Plus natively allow queries to run from the shell itself?

For example, is there equivalent of these in SQL*Plus sqlplus 'SELECT * FROM emp' | less sqlplus 'SELECT * FROM emp' | grep Primx One way has been suggested by paxdiablo here. Is that the only way? ...