Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

Write a SQL statement to update the DueDate ofthe “INFO” students who borrow DVD items in the Loan table. The new
Duedate is the checkout date plus 7 days. (Hint: (1) use the DATE function; the
due date is the CheckedOut plus 7 days; (2) Use a subquery to find the
InventoryitemID using the DVD media code.)

Ive attached the database schema below
0
Nothing ever in the clear!
LVL 1
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

So I am trying to write a query to remove the records where the Customer has only one OverdueFee. (I have a Loan Table and a Customer Table which both contain the same column CustomerID which in the Customer table is seen as a Primary Key AUTOINCREMENT) So far my query has DELETE FROM Loan WHERE EXISTS (SELECT * FROM Customer WHERE Customer.CustomerID = Loan.CustomerID Loan and Customer are two separate tables. Loan contains LoanID, CustomerID, InventoryItemID etc.... and the Customer Table contains CustomerID as well as others that dont really matter in this case. The OverdueFee is a column within the Loan Table with the REAL data type
I am wanting to remove all records when the Customer has only ONE overduefee in the Loan table so im guessing I have to somehow JOIN both the two tables by the CustomerID? Tad stuck so any help would be appreciated
0
Hello,

When using SQL Express how do you go about running these items manually as Maintenance Plans aren't available?

•      Verify the Integrity of Database
•      Maintain a Database's Indexes
•      Maintain Index and Column Statistics
•      Reduce your transaction log
0
Hi experts, How to put a condition in a certain field (datetime). I have column named, theDateIn which has a data "2017-09-24 08:02:39.000", "2017-09-24 07:50:44.000". I'm trying to retrieve by using this code but it didn't work.

Select TheDescription from eCho where theDateIn Between '2017-09-24' And '2017-09-24' order by Theicnt asc

Please note that the code will work if i make  '2017-09-25' or september 25, in the end part but my existing data doesn't have 25th of september all are 24th only. I also notice that it also work  If i remove the time in the database. Which part of the code should need to add or change? thank you!
0
I have a very large project with a very tight deadline especially because of billable time. My client is wanting some payroll features to be added to the application (VB.NET/Web Forms). All the hours etc. are based on getting information from a view named vClaimLine. There are a few items on this list: An employee has a pay rate and event has a pay rate. An employee can have a pay override for an event. Meaning if an employee has an override in the EmpPlusEventOverride table then, this rate trumps their pay rate. So my question is am I able to add a column to the view using an additional join without screwing up anything? I know that seems like a loaded question but in theory anyway... This way in my code behind for a payroll report I could utilize that column to see if there is a value in it. If their is, then their pay will be set to either that value or that value times their hours.

I created three new tables, one to hold their base pay and one to hold an event rate and one to hold a combined set rate for the Employee Plus Event.
0
Hi experts,
   I have oracle 10g table for _Files which has a _filename field
    sample entries can be
      f1.xlsx
      file2.doc
      fl.h
      testfile.abcxyz

   I need a query that gives only the extensions . in this case the result should give
      .xlsx
      .doc
     .h
    .abcxyz

 Your help is appreciated
0
hi all,


I have the free edition of Esxi 6.5 U1. I want to convert Windows 2008 R2 std  with SQL service into the virtual machine.
what will be the best  way?
0
Is there a better way to write the final output and not use sub-queries?


 IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
create table #temp(
	fullName varchar(100) null,
	IP_PP varchar (100) null,
	payment int null
)

insert into #temp values
('Tom', 'IP', 1),
('Tom', 'PP', 4),
('Tom', 'PP', 47),
('Ed', 'PP', 10),
('Ed', 'PP', 20),
('Ed', 'IP', 20),
('Ed', 'IP', 30)

select 
	fullName,
	(select sum(payment) from #temp b where a.fullName = b.fullName and b.IP_PP = 'pp') PP,
	(select sum(payment) from #temp b where a.fullName = b.fullName and b.IP_PP = 'IP') IP
 from #temp a
 group by fullName

Open in new window

0
What is the best way to monitor for queries that run > xx seconds?  I would love to have a table / file that I can look at each morning to see if there are any issues.  I've read about event monitors but I did not see a way to restrict them to a certain time range and we have so many queries that run, I am afraid to capture ever SQL statement that runs.  I think that would blow out my storage pretty quickly.

What is the most common way to do this with DB2?

Thank you!

Jim
0
Is it possible to schedule a job to run every hour, but at a specific time every hour?

For example, i would want to run the job at 1:02, 2:02, 3:02, 4:02 etc...

Right now, the SQL scheduler wizard only allows me to select the top of every hour for a specific time-frame.
0
Will your db performance match your db growth?
LVL 3
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

I have an Oracle database that I want to extract data from with four columns called U_TIME, KEY, VALUE and QUALITY.  I need the data for 334 entries using the KEY column. File #1 should contain the last 6 hours of information from the list of 8 digit KEY's.  The second file should contain the KEY where the field named QUALITY has a value of 256 but queries data for the last 45 days.  VALUE is a float and QUALITY is an integer.  
The KEY looks like this:

03031024, 03031027, 03031030, 03039002, 03039004, 03040003, 03040005, 03041002

and must have the leading 0:


The output files should look like:

File #1
9/1/2017      1:00      03019002      12      0
9/1/2017      2:00      03019002      14      0
9/1/2017      3:00      03019002      16      0
9/1/2017      4:00      03019002      25      0
9/1/2017      5:00      03019002      10      0
9/1/2017      6:00      03019002      22      0
9/1/2017      1:00      03019025      34      0
9/1/2017      2:00      03019025      20      0
9/1/2017      3:00      03019025      11      0
9/1/2017      4:00      03019025      52      0
9/1/2017      5:00      03019025      26      0
9/1/2017      6:00      03019025      39      0
.
.
.                        

File #2
9/11/2017      4:00      03019002      0      256
9/14/2017      2:00      03068025      0      256
9/27/2017      12:00      03052045      0      256
.
.
.
0
Hi Experts,

I am having a enormous amount of intrusion attempts on our cloud web and sql servers.  It's a different IP address all the time.  I am currently researching this further but not having much luck on how to close up the vunerability or stop it.

Any ideas how I can stop this?  My mailbox is flooded with these notifications and it's increasing.

We use Symantec Endpoint Security, A firewall, and the windows firewall

Thank you,

Karen
0
I am looking for hosting server recommendations.

Our project will involve:

ASP.NET framework
SQL Server database
Xamarin mobile app

Load Balancer
Firewall

Recommendations?

Thanks!
0
I have created an Access update/delete query for a database designed to keep track of RSVP's for events.  I have created a delete query to reset the RSVP yes/no/no response field

DELETE Invitations.RSVP
FROM Invitations
WHERE (((Invitations.EventID)=(Forms!Events!Invitations_Subform!EventID)));

I thought that it would only delete the RSVP field but it instead deletes all of the fields in the form including Name, Spouse Name, etc.  How can I do a delete query that only deletes the information in the RSVP column?
0
Hello:

In SQL, I created a Windows Authenticated login for myself.  I gave that login access to only one SQL database.

Yet, when I use Microsoft Query in Excel, I get a login error message when trying to access this database.  The only way "around this" is to check the box for "Use Trusted Connection".  Why is that?

Secondly, even when using this checkbox, I get access to all of the SQL databases in the instance--not just the one database that I gave myself access to.  Why?

In any case, my Team and I ultimately used a SQL authenticated-login for Excel's Microsoft Query.  But, doesn't doing so represent a security loophole?

Thanks!

John
0
Hi
Can't fix this query:
InstantsQuantity: Sum(IIf([eventstatusC]="future" And [Forms]![frmAddItems1]![txtDateFromTime]>([EventDateFrom]+[EventDateFromtime]) And [Forms]![frmAddItems1]![txtDateFromTime]<([EventDateUntil]+[EventDateuntilTime]) Or [Forms]![frmAddItems1]![txtDateUntilTime]>([EventDateFrom]+[EventDateFromtime]) And [Forms]![frmAddItems1]![txtDateUntilTime]<([EventDateUntil]+[EventDateuntilTime]),[RentalInnerProductQuantity],0))
0
Access 2010 query to sql linked tables.

SELECT dbo_t_nsc_trackcode_assigned_DataEntry.NSC_Id, 
dbo_t_nsc_trackcode_assigned_DataEntry.ID_Racfid, [EMPLOYEE_FIRST_NAME] & " " & [EMPLOYEE_LAST_NAME] AS Name, 
dbo_t_nsc_trackcode_assigned_DataEntry.Opened_Date, dbo_t_nsc_trackcode_assigned_DataEntry.Closed_Date, 
dbo_t_nsc_trackcode_assigned_DataEntry.Col_Id_Template, 
dbo_t_nsc_trackcode_assigned_DataEntry.Activity_Task, Workdays([Opened_date],[Closed_Date]) AS [Working days], 
[Service_Level_Hours]/24 AS STATED_SLA_HOURS, 
dbo_t_nsc_support_type_column_template_v2.Standard_In_Minutes

FROM (dbo_t_nsc_trackcode_assigned_DataEntry INNER JOIN dbo_t_nsc_support_type_column_template_v2 ON 
dbo_t_nsc_trackcode_assigned_DataEntry.Col_Id_Template = dbo_t_nsc_support_type_column_template_v2.Column_ID) 
INNER JOIN dbo_employee_directory ON dbo_t_nsc_trackcode_assigned_DataEntry.ID_Racfid = dbo_employee_directory.user_id
WHERE (((dbo_t_nsc_trackcode_assigned_DataEntry.Closed_Date) Is Not Null))
ORDER BY dbo_t_nsc_trackcode_assigned_DataEntry.NSC_Id DESC;

Open in new window



What I need:
For this field in the query:
[Service_Level_Hours]/24 AS STATED_SLA_HOURS,

If the value is  "1", i need it displayed as "0" if possible.


Thanks
fordraiders
0
I am trying to make a DELETE query. I want to DELETE all fields in a table for every record found in a query.

Query:
DELETE *
FROM ImportChildrenPartsCSV
WHERE (ImportChildrenPartsCSV.IDKey)=[qryImportFindDuplicatesDELETE].[IDKey];

Open in new window


(ImportChildrenPartsCSV.IDKey) is the table
and
[qryImportFindDuplicatesDELETE].[IDKey] is the query

Everytime I run it. It asks for the parameter for
[qryImportFindDuplicatesDELETE].[IDKey]

Thought this was pretty straight forward but I don't get it. Thanks for the help.
0
I have been asked to generate Reports from lots of tables in DB2.

I want to learn to solve writing queries from simple to complex through lots of problems and suggested the solution.
0
Prepare for your VMware VCP6-DCV exam.
LVL 1
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

I have this query that works great. As soon as I try to add Group By pat.PATNUM it breaks and no data displays. Is there a different way to use Group By with mssql?  I put in Group By pat.PATNUM right before the ORDER BY

SELECT info.Suite, info.PID, info.NID, info.TTF, info.Discharged,, pat.TASK,info.PATIENTID, pat.PATNUM, pat.SCHDT FROM admissions.info as info INNER JOIN admissions.patientinfo as pat ON info.PATIENTID=pat.PATNUM WHERE info.Suite IN (7,8,9,10,11,12,13,14,15) AND info.Discharged IS NULL  AND pat.DNU !='1' AND pat.TASK !='Z CO SURGEON' ORDER BY info.Suite ASC

Open in new window

0
I have to take current date and a field from my table and need to calculate number of days in between  two dates using SQL in MS ACCESS

My Query is

UPDATE AllTickets SET Aging = (select DATEDIFF("d",NOW(),CreateTime) from AllTickets  )
WHERE [ReferenceNo] = 176698;

CreateTime is an Field in my table and it will have value like "8/12/2014 11:37:57 AM"
AllTicket is my Table name

Can anyone suggest me good solution?
0
Please show example to populate a text box with last I'd from a sql table(e.g. Employee)
0
Backup was running fine for a couple of months until recently. Error: VSSControl: Failed to freeze quest over network, wait timeout.

Restarted server, repaired SQLVSS Writer, increased timeout as recommended by Veeam, changed service to domain administrator, VsPhere able to take snap and another backup software able to do application aware backup.
0
Hi,
I have an insert trigger that uses a CLR function to retrieve values to insert in.

If I try the insert outside the trigger, it runs fine or retrieves data using the CLR function.

But when I create a trigger with the CLR function within, get a timeout error.

Why won't it work within a table trigger?  And works just fine by itself?

Permissions issue?

Thanks

Error getting -
Msg 6522, Level 16, State 1,
System.Net.WebException:  The operation has timed out

Database - Sql Server 2008
0
We have SCCM current branch 1706, with a SQL 2012 server on the back end running SSRS. I would like to allow our Service Desk access to create their own reports in one specific folder only - protecting all the other folders containing the standard SCCM reports.

Is there a way to define a security scope that's SSRS folder aware? I don't think I can do this from the SSRS side. I believe SCCM sets all permissions via policy on those SSRS folders every 15 minutes.
0

SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.