SQL query question help

RPA Discussions
,
1

The company needs a list of all software licences that have an expiry date on or before 31/12/2019.

Write an SQL query to return the fields CustomerID,
SoftwareID, LicenceType, Cost and ExpiryDate for all
licences that expire on, or before 31/12/2019.
Group the output by CustomerID, and in ascending order of cost.

CREATE TABLE softwarelicenses
SELECT CustomerID, SoftwareID, LicenceType, Cost, ExpiryDate
FROM softwarelicenses;
WHERE ExpiryDate <= #31/12/2019#)
GROUP BY CustomerID;
ORDER BY Cost ASC

So far I have this, I know it isn’t correct but any help would be much appreciated :slight_smile:

2

Hi ,
The query is correct based on the required .
2 clarifications required are

  • why a new table is created ?
  • the expiry date column is of what type?

Also the # is to be replaced by single quote.

3

Hi priyanka thank you your response, I’m not sure i am new to writing SQL query’s and I am struggling with this homework question. Spent hours studying and just not fully getting my head around it.
Are you saying, it should look like this,without a new table?

SELECT CustomerID, SoftwareID, LicenceType, Cost, ExpiryDate
FROM softwarelicenses;
WHERE ExpiryDate <= ‘31/12/2019’)
GROUP BY CustomerID;
ORDER BY Cost ASC

1 Like
4

Yes , exactly this query is correct

what is the data type of ExpiryDate that you have used in original Create table.

5

I don’t know i dont have an orginal answer because I have guessed it. but now i am confused because surely FROM softwarelicenses is now incorrect as i have not done create table.
The only information for the question I have been given is this:

'The company needs a list of all software licences that have an expiry date on or before 31/12/2019.

Write an SQL query to return the fields CustomerID,
SoftwareID, LicenceType, Cost and ExpiryDate for all
licences that expire on, or before 31/12/2019.
Group the output by CustomerID, and in ascending order of cost.’

6

For this question, This would be the answer:

SELECT CustomerID, SoftwareID, LicenceType, Cost, ExpiryDate
FROM softwarelicenses;
WHERE ExpiryDate <= ‘31/12/2019’)
GROUP BY CustomerID, SoftwareID, LicenceType, Cost, ExpiryDate
ORDER BY Cost ASC

7

thanks so much for your help much appreciated :slightly_smiling_face:

1 Like
8

Hi @jasmine-demi,
Is it working as per requirement?

If so please mark it as solution.

9

Yes, Thank you :slight_smile: :slight_smile:

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.