Which of the below select statement is used to select all columns of emp table

Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

---

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

---

SELECT Column Example

The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table:

{"ad_unit_id":"App_Resource_Sidebar_Upper","resource":{"id":9296997,"author_id":4142577,"title":"Retrieving Data Using the SQL SELECT Statement","created_at":"2017-06-12T15:54:46Z","updated_at":"2017-06-14T01:58:43Z","sample":false,"description":"List the capabilities of SQL SELECT statements\r\nExecute a basic SELECT statement","alerts_enabled":true,"cached_tag_list":"","deleted_at":null,"hidden":false,"average_rating":null,"demote":false,"private":false,"copyable":true,"score":15,"artificial_base_score":0,"recalculate_score":false,"profane":false,"hide_summary":false,"tag_list":[],"admin_tag_list":[],"study_aid_type":"Quiz","show_path":"/quizzes/9296997","folder_id":8995623,"public_author":{"id":4142577,"profile":{"name":"mmmylaisa","about":null,"avatar_service":"gravatar","locale":"en-US","google_author_link":null,"user_type_id":158,"escaped_name":"Mylaisa McMullen","full_name":"Mylaisa McMullen","badge_classes":""}}},"width":300,"height":250,"rtype":"Quiz","rmode":"canonical","sizes":"[[[0, 0], [[300, 250]]]]","custom":[{"key":"rsubject","value":"Digital Technologies"},{"key":"rlevel","value":"Praxis"},{"key":"env","value":"production"},{"key":"rtype","value":"Quiz"},{"key":"rmode","value":"canonical"},{"key":"sequence","value":1},{"key":"uauth","value":"f"},{"key":"uadmin","value":"f"},{"key":"ulang","value":"en"},{"key":"ucurrency","value":"usd"}]}

{"ad_unit_id":"App_Resource_Sidebar_Lower","resource":{"id":9296997,"author_id":4142577,"title":"Retrieving Data Using the SQL SELECT Statement","created_at":"2017-06-12T15:54:46Z","updated_at":"2017-06-14T01:58:43Z","sample":false,"description":"List the capabilities of SQL SELECT statements\r\nExecute a basic SELECT statement","alerts_enabled":true,"cached_tag_list":"","deleted_at":null,"hidden":false,"average_rating":null,"demote":false,"private":false,"copyable":true,"score":15,"artificial_base_score":0,"recalculate_score":false,"profane":false,"hide_summary":false,"tag_list":[],"admin_tag_list":[],"study_aid_type":"Quiz","show_path":"/quizzes/9296997","folder_id":8995623,"public_author":{"id":4142577,"profile":{"name":"mmmylaisa","about":null,"avatar_service":"gravatar","locale":"en-US","google_author_link":null,"user_type_id":158,"escaped_name":"Mylaisa McMullen","full_name":"Mylaisa McMullen","badge_classes":""}}},"width":300,"height":250,"rtype":"Quiz","rmode":"canonical","sizes":"[[[0, 0], [[300, 250]]]]","custom":[{"key":"rsubject","value":"Digital Technologies"},{"key":"rlevel","value":"Praxis"},{"key":"env","value":"production"},{"key":"rtype","value":"Quiz"},{"key":"rmode","value":"canonical"},{"key":"sequence","value":1},{"key":"uauth","value":"f"},{"key":"uadmin","value":"f"},{"key":"ulang","value":"en"},{"key":"ucurrency","value":"usd"}]}

The keyword CALCULATED is used to refer to a newly created variable for further calculation. In this case, we have used CALCULATED to sum 'N_MISSINGS' and 'N' variables.

18. KEEP and DROP some variables

Suppose you need to keep all the variables from SASHELP.CARS except variables 'MODEL' and 'MAKE'. The DROP= option is used to drop these two variables. Similarly, we can use KEEP= option to keep specific variables. These DROP= and KEEP= Options are not native SQL language. It only works in SAS.

proc sql;
create table saslearning (drop= make model) as
select * from sashelp.cars;
quit;

19. Delete Rows from a Table

You can use DELETE FROM  statement to remove records (rows) from a dataset.

proc sql;
delete from mylib.outdata
where momage > 0;
quit;

In this case, we are deleting all records having momage greater than 0 from outdata dataset. Log shows '478 rows were deleted from MYLIB.OUTDATA'.

20. Sub Query
Find employee IDs who have records in table file1 but not in table file2.

data file1;
input ID age;
cards;
1 24
2 34
3 45
4 67
;
run;

data file2;
input ID age;
cards;
1 25
3 46
4 62
;
run;

Proc SQL;
Select ID from file1
Where ID not in (select ID from file2);
Quit;

21. Sub Query - Part II
Find employee IDs whose age is in the average age +/- 10 years.

Proc SQL;
Select id from file1
where age between (select Avg(age) from file1) - 10 and
(select avg(age) from file1)+10;
Quit;

22. Sub Query - Part III

proc sql;
select Name, Grade, Teacher,
Case
When Student_ID in
(select Student_ID from Tests where Score lt 70) then 'Failed one or more tests'
else 'Passed all tests'
end as Test_Results
from Students;
quit;

Next Lesson : PROC SQL Joins

Proc SQL Tutorials : 15 Proc SQL Tutorials

SAS Tutorials : 100 Free SAS Tutorials

Spread the Word!
Share Share Tweet Subscribe

Related Posts

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.

Which SQL statement is used to SELECT all columns FROM the Employees table?

What is used in the SELECT clause to return all columns FROM a table?

Which statement will remove a column called job FROM the EMP table?

Which of the following symbols used to SELECT all columns or fields in a table?