sql

Basic MySQL Table Join?

I have these 2 tables: ASSOCIATION_TABLE: [id, key, name, manager, office, bank, customer] and OFFICE_TABLE: [id, name, address, phone] I am currently running this query to get a data set that I need: SELECT `name`, `key`, `office`, `manager`, `id` FROM `database`.`ASSOCIATION_TABLE` WHERE `association`.`customer`=4; How can I m...

SQL database queries basic question

Relational Schema: Employee (Enum, Ename) VentingMac (Enum, Cokename, day) The attribute Enum in VentingMac relation is a foreign key referencing the relation Employee. I want to list out all the employee names (Ename), who drink more than 3 (distinct) coke on the same day (assuming they wont drink the same coke each day) ...

how does the groupby and count work in sql

1> select browser,count(*) from logtest group by browser; +-----------+----------+ | browser | count(*) | +-----------+----------+ | Firefox 3 | 14 | | Unknown | 11 | +-----------+----------+ 2 rows in set 2> select browser,count(browser) from logtest group by browser; +-----------+----------------+ | browser | cou...

SQL Server query to return rank based on game results

I have looked at multiple other question similarly asked on StackOverflow, but nothing seems to fit my bill. My query is slightly more complex. Essentially, I need to find the rank of the entry. My table structure is: TestEntry Id, TotalTime, DateCreated GameResult GameId, TestEntryId, Duration, Score QuestionResult QuestionId, ...

How do you generate SQL using LINQ?

I want to be able to take a LINQ statement like this. var User = from u in Users where u.UserID == 7 select u.UserName; And have it generate SQL like this. SELECT UserName FROM Users WHERE Users.UserID = 7 I know LINQ TO SQL does this but I don't want all that added xml mapping and generated code. Obviously thi...

SQL round to the nearest hundredth.

UPDATE prodfeatures2 SET featureprice = featureprice * 0.6316; I am trying to setup a round of to the nearest hundredth. HELP! I do not want the answer to be 104.7648, I would want it to be 104.76. I do not want the answer to be 104.7668, I would want it to be 104.77. ...

sql server 2008 database diagrams table name search

Hi all, I have SQL server 2008 enterprise with a database containing up to 100 tables. I generated a database diagram with all of the tables because I want to get an overview of the database. My question is : How can I quickly find the diagram I want, are there any search functions regarding to diagram search ? ...

VBA While loop using an SQL statement as my While.

Hi there, I'm writing some code behind some spreadsheets and I need to loop through some code, like getting data from a database doing some formulas and moving the data to a new sheet. My code for getting the data from the database is getting all of the values in multiple columns where the data has not been reported and has the same fil...

SQL Server: Try to Delete

I have a bunch of data in a table that serves as the parent (primary key in foreign key relationship) to several other tables. I basically want to go through this table and try and delete records that do not have children. I have already deleted the children, so there are parents that are childless. If a record can be deleted, delete...

How can I do this complex SQL query using Django ORM? (sub-query with a join)

I'm used to writing my own SQL queries and I'm trying to get used to the whole ORM thing that seems to be so popular nowadays. Here's the query: SELECT * FROM routes WHERE route_id IN ( SELECT DISTINCT t.route_id FROM stop_times AS st LEFT JOIN trips AS t ON st.trip_id=t.trip_id WHERE stop_id = %s ) where %s is an intege...

HTML5 Web SQL DB - Maximum table size?

I'm part way through building a fairly complex HTML5 web app, that retrieves its data from a Web Service to populate the Web SQL databse. All was going well with the test data, then I tried using some real data (a table with around 10 cols and over 180,000 rows) and ran into some issues. I am sending the SQL responses in "chunks" of 50...

PHP MySQL - SQL Query Help

Hello, I have the following code to add users to a particular role. The code works fine but I'd like to change the SQL so it only inserts the user into a role if it doesn't already exist. Any help is greatly appreciated. Here is my code: // ------------------------------------------------------------------ // ADD SELECTED USERS...

subquery considered as table

how can do assume sub query as table in mssql? ...

SQL to batch re-tag items

I've got a MySQL database with typical schema for tagging items: item (1->N) item_tag (N->1) tag Each tag has a name and a count of how many items have that tag ie: item ( item_id (UNIQUE KEY) ) item_tag ( item_id (NON-UNIQUE INDEXED), tag_id (NON-UNIQUE INDEXED) ) tag ( tag_id (UNIQUE KEY) name count ) I need to writ...

Inserting UNICODE characters in Sql .

Hi , I am trying to get unicode strings into an SQL*Plus: Release 10.2.0.2.0 database but am having difficulties. If I use SQLPlus and copy and paste the insert statement into the database, any special characters are inserted as ? or something like that. I then try to call a sql file that has been encoded to UTF-8 and the outcome is th...

Help with SQL aggregate functions

Hi, I've been learning SQL for about a day now and I've run into a road bump. Please help me with the following questions: STUDENT (**StudentNumber**, StudentName, TutorialNumber) TUTORIAL (**TutorialNumber**, Day, Time, Room, TutorInCharge) ASSESSMENT (**AssessmentNumber**, AssessmentTitle, MarkOutOf) MARK (**AssessmentNumber**, **Stud...

How do I insert data into object tables that have refs to others?

I'm new in Oracle and I really don't have a clear idea how to do this. The database is this one... CREATE OR REPLACE TYPE personUdt4 AS OBJECT( pid varchar(11), firstName varchar(20), lastName varchar(20), dob date) NOT FINAL; / CREATE OR REPLACE TYPE locationUdt4 AS OBJECT( street varchar(30), bldg varchar(5), room varchar(5)...

count(*) where cond = val, or count(cond = val)

What is the difference between these two methods? select count(*) from `table` where `column` = 'value'; and select count(`column` = 'value') from `table`; To me they seem to do the same thing; I assume this is not the case for the database. Does one method leverage indexes better than the other? In this case I use MySQL but a gen...

Use of StoredProcs in an application

hi. we're in the process of designing an enterprise application & our technical architects suggest that "lets have no sql queries in the code. even if its a simple select call to the database a stored procedure should be written for it in the database & the code should call it." my issue with this is that it seems like a dumb idea to ha...

Problem: Group BY clause showing results previously filtered out by where clause

Hi, I have something like this select A, B, C from tableA where A = '123' group by B and the results include entries whose A is not '123'. Why is this not the results I expected it to be? thanks database has 16k entries actual result (7k entries): a mixture of entries with A='123' and A='other' expected results (5k entries):...